Data Validation - using a list from another workbook

[PeterW]

Hi

I am trying to establish data validation in "WorkbookA" from anothe
workbook "WorkbookB".

The location of WorkbookB may change from time to time, but will alway
be located in same path as WorkbookA, but under a different subroot, sa
the "data\" subroot. The rangename that holds the data to be validate
is "List_Months" in WorkbookB.

I have created a range in WorkbookA called "MyList" by adding th
following formula to the "refers to" box,
="'"&LEFT(INFO("directory"),FIND("Data\",INFO("directory"))-1)&"WorkbookB"&".xls'!"&"List_Months2"

When I attempt to use the name under the "list" option in Dat
Validation I receive the message "the list source must be a delimite
list or a reference to a single row or column."

Thanks in advance for your assistance
Pete

 

[Biff]

Hi!

This can't be done (at least, not the way that you want).

You can't refer to another file' defined range name. You would have to build
the defined name in the current file that refers to the other file but in
order to do that you would have to use the Indirect function. In order for
the Indirect function to work the other file MUST be open. So, the drop down
will only work as long as the other file is open which probably defeats the
purpose.

Biff

 

[PeterW]

Hi Biff

Thanks for the response. I am happy for the other file to be open a
the same time, however I can't use INDIRECT as it doesn't allow th
file path to be selected.

Any other work around would be appreciated.

Pete

 

[Biff]

Hi!

As the source for the drop down use something like this:

=INDIRECT("'C:\TV\[Test1.xls]Sheet1'!A1:A5")

Biff

 

[PeterW]

Thanks Biff ... that works