Data Validation - using a list from another workbook
 

Hi

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

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

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

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

Thanks in advance for your assistance
Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=493675

Data Validation - using a list from another workbook
 

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" wrote in
message ...

Hi

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

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

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

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

Thanks in advance for your assistance
Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile:
http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=493675

Data Validation - using a list from another workbook
 

Hi Biff

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

Any other work around would be appreciated.

Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=493675

Data Validation - using a list from another workbook
 

Hi!

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

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

Biff

"PeterW" wrote in
message ...

Hi Biff

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

Any other work around would be appreciated.

Peter


--
PeterW
------------------------------------------------------------------------
PeterW's Profile:
http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=493675

Data Validation - using a list from another workbook
 

Thanks Biff ... that works


--
PeterW
------------------------------------------------------------------------
PeterW's Profile: http://www.excelforum.com/member.php...fo&userid=6496
View this thread: http://www.excelforum.com/showthread...hreadid=493675