Using Validation List from Another Workbook with Dependent Data

[Mike R.]

Hello,
I have several workbooks that share the same data for data validation. I
have been success with just about all of the shared data. I am hitting a
snag with just one important datavalidation list. This is a dependent list.
The list from column B depends on what was entered in column A. I have
linked all of the data name fields/lists, but it still won't work. In my
validation for coulmn B, I have the source as:
=INDIRECT(VLOOKUP(A1,DeptLookUp,2,0))

What should I be doing differently? I have read everything on Contextures,
but there is nothing about this particular situation.... help please..
thank you,

 

[Debra Dalgleish]

You could define a name that uses the VLookup to find the range in the
other workbook. For example, define a name TestList, with the formula:

=INDIRECT("MyListsWkbk.xls!"&VLOOKUP(Sheet1!$A$1,DeptLookup,2,0))

In the Data Validation dialog box, refer to that name:

=TestList

 

[Mike R.]

Debra,
this works PERFECT. Thank you so much,
Mike

 

[Mike R.]

Ok...I spoke too soon...I hit a snag. When I define the name it works great
for one row with the $A$1 part...but I have many rows. How can I define a
name when changing rows?
Thanks,

 

[Mike R.]

ok...got it work...thanks

Ok...I spoke too soon...I hit a snag. When I define the name it works great
for one row with the $A$1 part...but I have many rows. How can I define a
name when changing rows?
Thanks,

 

[Debra Dalgleish]

You're welcome. Thanks for letting me know that you've got it working.