Can I create a named range in WB1 from a named range in WB2?

Summary:
Can I create a named range in workbook1 that references a named range in workbook2?

Details:
I want to implement data validation on a number of columns in WB1. There will be one copy of WB1 for each user, since shared workbooks suck.
I want to store my lookup ranges in WB2. I want to share WB2. The lookup workbook (WB2) will be opened as a hidden workbook whenever WB1 is opened.
If I need to update the lookups, I want to open WB2, define the named range, and have the named ranges in WB2 reflect the new range.
Rather than having to redefine the data validation range for each copy of WB1.
IOW as soon as I add a new row to the lookup range in WB2, then redefine that named range, the dropdown list in the validated column reflects the new data.

Can this be done?

Kinda hard to follow your request, but if I get this right, you can create a tab on WB1 that each cell is equal to the cells of WB2 and define that as your range. Then hide the tab. Use your vba to close the hidden WB2 as it is no longer needed after it updates the hidden tab on WB1.

Originally Posted by jwlamb

Kinda hard to follow your request, but if I get this right, you can create a tab on WB1 that each cell is equal to the cells of WB2 and define that as your range. Then hide the tab. Use your vba to close the hidden WB2 as it is no longer needed after it updates the hidden tab on WB1.

Thanks for the quick reply. Much appreciated.

What I tried:

* In WB2 (the lookup workbook), I created a named range Lookup1.
* In WB1 (the source/data entry workbook), I created a named range Lookup1. I navigated to the same range as Lookup1 in WB2.
* In WB1, I setup data validation, and tried to use =Lookup1 as the source, but got an error message "You cannot use references to other worksheets or workbooks for Data Validation criteria"

This seems to contradict http://office.microsoft.com/en-au/ex...010102338.aspx.

Regardless of the implementation details, what I want to do is:

* Have 3-10 users have their individual copies of their data entry workbooks open. (I've determined that Shared Workbooks suck, but didn't want to be too negative on my 4th post to this community ;-) )

* Have the data validation lookups defined on a centralised, 2nd shared workbook.

[I assume it needs to be shared in order to avoid locking problems on the lookup workbook, and in order to do dynamic updates of the lookup criteria without having to close the multiple data entry workbooks. If, instead, I can open the lookup workbooks in readonly mode (via VBA), but be able to update the lookups while that workbook is open in the data entry workbooks, that would be great. I want to avoid shared workbooks if possible. I'll try testing opening a workbook readonly, then going to another machine and seeing if I can then update that workbook.]

* My understanding is the lookup workbook has to be open for data validation to work, so I intend to open the lookup workbook as a hidden workbook.

* Finally, I want to be able to dynamically update the lookup criteria, while the data entry workbooks are open, and have those changes reflected in the data entry workbook.

[When I tested this, even when I updated the named range in the lookup workbook, the range for the data validation was "hardcoded" in the first workbook. I need that range to be dynamic.]

I hope this makes sense?

I think I've got this working via this post: http://www.mrexcel.com/forum/excel-q...down-list.html

I created a "Lookups" tab on my current workbook, with text in Column B2 downward.

I created a "Lookups" tab on another workbook (called "Lookups.xlsx"), with the same setup as above. Note there are multiple tabs on that workbook.

I created this dynamic named range "Lookups1" in my current, data entry workbook (same workbook, Lookups worksheet):

=OFFSET(Lookups!$B$2,0,0,COUNTA(Lookups!$B:$B)-1,1)

and was able to use the dynamic named range for data validation (=Lookups1 for the data validation source). The data validation was dynamic as I edited the lookup data.

I then created this dynamic named range "Lookups2" in my current workbook:

=OFFSET([Lookups.xlsx]Lookups!$B$2,0,0,COUNTA([Lookups.xlsx]Lookups!$B:$B)-1,1)

and was also able to use it for data validation. It too was dynamic as I edited the range on the other (open) workbook.

At one time, I only had the single Lookups tab in the Lookups.xlsx workbook. I was thrown off by the fact that the worksheet was dropped from the dynamic range specification when I saved it. I guess "no worksheet name" means "this first (and only) worksheet". Not the most intuitive. Once I added the 2nd worksheet, the dynamic range specification no longer changed from what I entered once I saved it.

I later worked out a simpler way to specify the dynamic named range:

=OFFSET(INDEX(Lookups.xlsx!Lookup1,2),0,0,COUNTA(Lookups.xlsx!Lookup1)-1,1)

Where I had defined a named range "Lookup1" in Lookups.xlsx as =Lookups!$B:$B.

The INDEX function returns cell $B$2 from the remote named range. The remote named range itself returns the entire column. And, I don't need to specify the workbook name in brackets (at least while I have it open, which is required for data validation).

I then changed the remote named range (in Lookups.xlsx) to =Lookups!$B$1:$B$3, and the data validation changed without having to modify anything on Book1. Although, with the OFFSET/INDEX functionality, I don't need to mess with the remote range. I can just define the entire column, and the formula takes care of everything, stopping at the first blank row, which is fine by me.

Finally, I changed Lookups.xlsx to a Shared Workbook, closed everything, then reopened both workbooks. The data validation still worked. I assume I can change the data validation lists in Lookups.xlsx while other users have Book1 open, but I'll have to test that once I get into work and am on the network.

Thanks again @jwlamb for your quick reply. This post was the reason I joined this forum http://www.excelforum.com/the-water-...est-forum.html.

Let me know if you see any issues with this approach.

Let me know if you see any issues with this approach.

Can anyone tell me how to make the above approach case-sensitive?

<RANT>
I've just spent hours Googling this issue, and can only conclude: Why can't Microsoft get this right? How bloody hard can it be to add a checkbox "Case-sensitive match" to "Ignore blank" and "In-cell drop down"? It's not remotely hard technically. Don't they get how their users want to use the product? And don't they even read years and years worth of forum posts running into the same data validation issues over and over again??? There's probably a convoluted, non-intuitive workaround, but Microsoft sure don't make it easy.


</RANT>