• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine 2 to 3 Named Ranges into a master Named Range

E

elmerg

New Member
Hey all. Back Again.

I'm trying to figure out how to take a set of named ranges and combine them into one contiguous range. The reason I'm using named ranges rather than cell references is because two of the ranges will change, based on data entered in two other cells, and an unchanging master list.

I need to figure out a way, preferably with functions and not VBA, to combine all this into one contiguous range. I'm attaching a file, but here's a base explanation

Dropdown A is chosen to be A
Dropdown B is chosen to be B

That would cause a combination of ListA, ListB, and MasterList into FullList range.

However, it also needs to work as...

Dropdown A is chosen to be A
Dropdown B is left blank.
FullList = ListA, MasterList

And

Dropdown A and B are left blank
FullList=MasterList

Any thoughts?
 

Attachments

  • Combining Named Ranges Example.xlsx
    10 KB · Views: 28
bobhc said:
Good day elmreg

As per the upload?
Click to expand...


I don't think so.

You managed to combine them but I can't even tell how you did, and it doesn't seem to do anything based on the first two drop downs. What I need is the data validation list to be dynamic, always consisting of one named range and then another named range that is chosen based on the content of another cell, which changes from a data validation drop down.

Let me see if I can make this a little clearer.

The first drop down box has four choices: A, B, C, and D. These correspond to lists AList, BList, CList, DList. I need the FinalList to populate from those lists, based on the choice made in the drop down via data validation, and the BaseList named range.

So if you have A chosen, the FinalList would be the contents of AList and BaseList.
If you choose B, the FinalList should be the contents of BList and BaseList.

And so on and so forth.
 
Hi ,

Can you clarify whether all of the named ranges ( AList, BList, CList, DList, BaseList ), are dynamic ? Will they have a fixed number of rows , or can they expand over time ?

Narayan
 
Hi Narayan,

Expansion over time would be ideal, but currently each one is slightly different, ranging from 3 to 20-odd entries. Generally BaseList should stay the same at 20-odd entries, but the others might change.
 
Well, I managed to get it to work based on another excel sheet I found. Yay for that.

Now, however, I can't get the data validation to come up as dynamic. It will add and remove the appropriate fields every time based on the drop down choice. I've done some experimenting and can get it to have as few as a single blank, or up to five blanks at the bottom.

I'm okay with this, at least for the moment.
 
I came to this seven-year-old thread in search of answers to the questions raised herein. I am delighted to report that I solved the problem that brought me here with the help of the example spreadsheet attached to the original message, along with a template sheet that displays the definitions of all named ranges in a custom worksheet. It's a template that I insert into all but the simplest workbooks that I create.
 
You must log in or register to reply here.
Back
Top