List from another workbook using INDIRECT

[Didehvar]

Hello,

I'm hoping someone can please help me with this?

If I Create a new workbook and use a validation list with values that match defined names, then create an additional list in an adjacent cell that uses an =INDIRECT(CELL_RANGE) this works fine. E.g:

Create two lists on the sheet and define one list as List1 and the other as List2
Cell A1 - Data - Validation - Allow = List & Source = List1, List2
Cell B1 - Data - Validation - Allow = List & Source = =INDIRECT(A1)
If I now select List1 from the list in cell A1 then the list contents for my defined List1 are populated in the list in B1.

My issue is that I am accessing lists from an external workbook, I can access the list if I specify the source as =List1, but I need to determine which list to read from based on another list in my spreadsheet. Here is the scenario (hopefully a bit clearer):

Workbook2:
Create a list with List1No1 to List1No10 in A1 - A10
Create another list with List2No1 to List2No10 in B1 - B10
Insert - Name - Define - Name = List1 & Source = =Sheet1!$A$1:$A$10
Insert - Name - Define - Name = List2 & Source = =Sheet1!$B$1:$B$10

Workbook1:
Insert - Name - Define - Name = List1 & Source = =Workbook2.xls!List1
Insert - Name - Define - Name = List2 & Source = =Workbook2.xls!List2
Cell A1 - Data - Validation - Allow = List & Source = List1, List2

This is where I hit the problem...
This works fine:
Cell C1- Data - Validation - Allow = List & Source = =List1 - You can see the contents of List1 from Workbook2

This doesn't work?:
Cell B1- Data - Validation - Allow = List & Source = =INDIRECT(A1) - This is the same principal as it working for the earlier example in the same sheet, but I just get a "The Source currently evaluates to an error..." message and the list is empty.

Is there something I should be doing or is my approach just completly wrong?

Really appreciate any help on this...

Kind regards,
Mark

 

[Andrew Poulsom]

Welcome to MrExcel.

What's in A1 when you get that error? Is Workbook2 open?

 

[Didehvar]

Thanks!

Cell A1 has List1 selected from the list and the other workbook is open, the sheet that I have my real solution in contains a macro that opens the workbook containing the list upon opening the workbook.

 

[Andrew Poulsom]

I can confirm that it doesn't work (Excel 2003) - INDIRECT can't resolve the reference to the other workbook. Sorry, I don't know a solution.