Using Validation List from Another Workbook with Dependent Data

G

Guest

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,
 
D

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
 
G

Guest

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,
 
G

Guest

ok...got it work...thanks


Mike R. said:
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,
 
Joined
Sep 22, 2013
Messages
1
Reaction score
0
Hi, Mike and Debra,
I am with the same problem, but i have some dificult with the formulas. I tried very much and studied a little about formulas, but can not reach any result. Can you spend some time and help me. I`m new in Excel. I attached 2 files .xlsx, to facilitate your teaching. My Excel is in portuguese and I related the name of the formulas. This place is the unique talking about dependent validation with lists in another workbook. Thanks in adivance. MarcoLau:bow:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top