Using Validation List from Another Workbook with Dependent Data
 

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,

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,D eptLookup,2,0))

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

=TestList

Mike R. wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

"Debra Dalgleish" wrote:

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,D eptLookup,2,0))

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

=TestList

Mike R. wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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." wrote:

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

"Debra Dalgleish" wrote:

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,D eptLookup,2,0))

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

=TestList

Mike R. wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

ok...got it work...thanks


"Mike R." wrote:

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." wrote:

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

"Debra Dalgleish" wrote:

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,D eptLookup,2,0))

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

=TestList

Mike R. wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

Mike R. wrote:
ok...got it work...thanks


"Mike R." wrote:


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." wrote:


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

"Debra Dalgleish" wrote:


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,D eptLookup,2,0))

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

=TestList

Mike R. wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html