Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike R.
 
Posts: n/a
Default 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,
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #3   Report Post  
Mike R.
 
Posts: n/a
Default

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


  #4   Report Post  
Mike R.
 
Posts: n/a
Default

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


  #5   Report Post  
Mike R.
 
Posts: n/a
Default

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




  #6   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
List, Data Validation, unlocked cell, protected sheet..... Kane New Users to Excel 6 July 16th 12 09:11 AM
Auto scroll down data validation list [email protected] Excel Discussion (Misc queries) 4 January 28th 05 07:44 PM
Data Validation List =Name Pitbull Excel Discussion (Misc queries) 3 December 30th 04 07:43 PM
Data Validation list selection question Bob Wall Excel Worksheet Functions 2 December 4th 04 05:51 PM
Make Data validation List Alphabetical?? Slumbering Gorilla Excel Worksheet Functions 6 November 19th 04 10:49 PM


All times are GMT +1. The time now is 12:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"