software

Hi

I am trying to establish data validation in quot;WorkbookAquot; from another
workbook quot;WorkbookBquot;.

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 quot;data\quot; subroot. The rangename that holds the data to be validated
is quot;List_Monthsquot; in WorkbookB.

I have created a range in WorkbookA called quot;MyListquot; by adding the
following formula to the quot;refers toquot; box,
=quot;'quot;amp;LEFT(INFO(quot;directoryquot;),FIND(quot;Data\quot;,INFO(quot;dir ectoryquot;))-1)amp;quot;WorkbookBquot;amp;quot;.xls'!quot;amp;quot;List_Months2quot;

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

Thanks in advance for your assistance
Peter--
PeterW
------------------------------------------------------------------------
PeterW's Profile: www.excelforum.com/member.php...foamp;userid=6496
View this thread: www.excelforum.com/showthread...hreadid=493675Hi!

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

quot;PeterWquot; gt; wrote in
message ...
gt;
gt; Hi
gt;
gt; I am trying to establish data validation in quot;WorkbookAquot; from another
gt; workbook quot;WorkbookBquot;.
gt;
gt; The location of WorkbookB may change from time to time, but will always
gt; be located in same path as WorkbookA, but under a different subroot, say
gt; the quot;data\quot; subroot. The rangename that holds the data to be validated
gt; is quot;List_Monthsquot; in WorkbookB.
gt;
gt; I have created a range in WorkbookA called quot;MyListquot; by adding the
gt; following formula to the quot;refers toquot; box,
gt; =quot;'quot;amp;LEFT(INFO(quot;directoryquot;),FIND(quot;Data\quot;,INFO(quot;dir ectoryquot;))-1)amp;quot;WorkbookBquot;amp;quot;.xls'!quot;amp;quot;List_Months2quot;
gt;
gt; When I attempt to use the name under the quot;listquot; option in Data
gt; Validation I receive the message quot;the list source must be a delimited
gt; list or a reference to a single row or column.quot;
gt;
gt; Thanks in advance for your assistance
gt; Peter
gt;
gt;
gt; --
gt; PeterW
gt; ------------------------------------------------------------------------
gt; PeterW's Profile:
gt; www.excelforum.com/member.php...foamp;userid=6496
gt; View this thread: www.excelforum.com/showthread...hreadid=493675
gt;

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: www.excelforum.com/member.php...foamp;userid=6496
View this thread: www.excelforum.com/showthread...hreadid=493675Hi!

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

=INDIRECT(quot;'C:\TV\[Test1.xls]Sheet1'!A1:A5quot;)

Biff

quot;PeterWquot; gt; wrote in
message ...
gt;
gt; Hi Biff
gt;
gt; Thanks for the response. I am happy for the other file to be open at
gt; the same time, however I can't use INDIRECT as it doesn't allow the
gt; file path to be selected.
gt;
gt; Any other work around would be appreciated.
gt;
gt; Peter
gt;
gt;
gt; --
gt; PeterW
gt; ------------------------------------------------------------------------
gt; PeterW's Profile:
gt; www.excelforum.com/member.php...foamp;userid=6496
gt; View this thread: www.excelforum.com/showthread...hreadid=493675
gt;

Thanks Biff ... that works--
PeterW
------------------------------------------------------------------------
PeterW's Profile: www.excelforum.com/member.php...foamp;userid=6496
View this thread: www.excelforum.com/showthread...hreadid=493675