Dynamically Update Defined Names Range

[Rem0ram]

Hi All

My requirement is to Dynamically update the the range of an defined name when ever there is a change in the range values.

Have attached sample file for the same.

Many Thanks for the help in advance.

Best
Rem0

[Rem0ram]

Hi

Happy to provide more info if my requirement information is vague and hard to understand.

Best
Rem0

[Chris 53]

As a quick answer, I've just gone to sheet OU and named B24 as WMStart, and B28 as WMEnd. I then went into Name Manager and replaced the cell addresses with the range names for BD_WM.

Inserting a cell between WMStart and WMEnd and entering anything now means that this new item is included in the drop down list.

This any good to you?

[Rem0ram]

Hi Chris

Many Thanks for looking in to my query.

Yes, you were right but my requirement is the data in OU gets updated automatically and records may get removed or added, irrespective of that I want to select the range completely by its defined name range.

Best
Rem0

[Rem0ram]

Hi

Can some one look in to this?

Finding it difficult to make this happen in my project.

Best
Rem0

[Rem0ram]

Hi

I'm looking for some help on this?

Can anyone look in to this.

Best
Rem0

[Rem0ram]

Hi All

Any help / guidance will greatly help me.

Best
Rem0

[Idon]

Hi,

Try leaving blank rows for future entries. If what you have is a list of values and cannot leave blank rows you may have to create a column for each group to hold all possible options (and blank rows for future entries.) Then in each column put some code to read from the list and pick does that follow the desired pattern.

hope this helps.
-Idon

[Rem0ram]

Hi Idon

Thanks!

My constrain is I will have those values in single column only no option to split to multiple columns.

Best
Rem0

[JosephP]

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[Rem0ram]

Hi JosephP

Apologies!

Below is the link to the cross post.

http://www.vbaexpress.com/forum/showthread.php?t=45185

Best
Rem0

[JBeaucaire]

Only one named range is needed for this. As long as you group the code together the way you're doing so that there is SOME unique string that can be spotted in each group, you can simplify this down to a single Data Validation formula.

1) Create a named range called Codes the is the entire column B of the OU page.

2) Put the unique codes in column E. For instance:
E6: /BS
E7: /CFG
E8: /MSB
E9: /ULT
E10: /WM

3) Then the data validation settings in G6 are:

Allow: List
Source: =OFFSET(Codes,MATCH("*"&$E6&"*", Codes, 0)-1,,COUNTIF(Codes,"*"&$E6&"*"),)

Copy that cell downward.

[Rem0ram]

Hi Jerry

You absolutely Nailed it...

I achieved what I required.

Thanks a Trillion.

Best
Rem0

[JBeaucaire]

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[Rem0ram]

Hi Jerry

Thanks for notifying, however I would like to re-open the thread.

The problem I'm facing after implementing the offset formula is the file size.

The macro-enabled userform I have developed is 750KB and when it is circulated by user only partial of the form will get attached to an email.

Before using the offset formula, the partial excel file size is only 200kb but after including the offset formula the file size was around 980KB which totally makes the form a failure, as the main agenda of the userform is to have a very small excel file.

I have googled and found that Offset formula is Volatile and is there a way to tackle this?

Can you help me?

Best
Rem0

[JBeaucaire]

A single formula does not quadruple the size of your workbook unless you paste 1000s of them in your workbook.

Offset is volatile but only in the cells where it is being actively used. Unless you have 1000s and 1000s of them, I don't expect you'll notive a performance drop.

The sample file I uploaded was less than 50k. What's going on here? You have a something we can actually look at?

[Rem0ram]

Hi Jerry

Have attached the original file, review and advise.

Have sent the password used as private message to you.

Best
Rem0

[JBeaucaire]

I think you're ascribing effects that coincidental. You've only put a few OFFSET formulas into the Name Manager, so that can't be affecting the file size.

If I were to hazard a guess, the two reference sheets OU and GEO each have 500 rows of data with some lengthy formulas in them. That's where your growth is coming from.

But unlike you, I do not equate file size with efficiency. We once had a "form" that went from 300k to 1.5 mb, but it was 30 times more productive in its enlarged version. HD space is at an all-time low, so that's no longer an issue.

[Rem0ram]

Hi Jerry

Thanks Again for the inputs.

Is there a way to drop the defined names details after generating the email with partial sheets of the form?

Best
Rem0

[JBeaucaire]

Huh, what?