I want to create a workbook which reads the value from a particular named
range (Test) in a number of workbooks.

The actual formula is:

='Y:\Testing\TestSource001.xls'!Test

I would like to build this formula and then read the value using the
Indirect function but cannot seem to get Indirect to work on anything other
than on a worksheet level.

Does Indirect work for external links? I can find nothing which says it has
this limitation, but I cannot find any examples of external links.

Paul Smith

"Paul W Smith" wrote...
....
The actual formula is:

='Y:\Testing\TestSource001.xls'!Test
....
Does Indirect work for external links? . . .
....

The simple answer is NO.

The nuanced answer is yes, but only for external references into OPEN
workbooks. Unless you'd have ALL these other workbooks open in the
same Excel session (process), INDIRECT won't work for this.

Try the following.

1. Put a list of the relevant file pathnames into a single column
range, e.g., A3:A20.

2. Enter the following formula corresponding to the topmost file's
pathname, e.g., in B3 corresponding to A3.

="='"&A3&"'!Test"

3. Fill this formula down so there's a like formula for every file,
e.g., fill B3 down into B4:B20. These formulas should produce text
strings that look like external reference formulas.

4. Select the range of formulas (B3:B20), copy, and paste special as
values on top of itself. Now you should have text CONSTANTS that look
like external reference formulas.

5. With this range still selected, run Edit Replace, replacing =
with = (yes, replacing the = character with itself). This effectively
enters all these text constants as formulas.

Thanks Harlan,

Excellent solution... I have no idea how it works but it does - thanks.




"Harlan Grove" wrote in message
...
"Paul W Smith" wrote...
...
The actual formula is:

='Y:\Testing\TestSource001.xls'!Test
...
Does Indirect work for external links? . . .
...

The simple answer is NO.

The nuanced answer is yes, but only for external references into OPEN
workbooks. Unless you'd have ALL these other workbooks open in the
same Excel session (process), INDIRECT won't work for this.

Try the following.

1. Put a list of the relevant file pathnames into a single column
range, e.g., A3:A20.

2. Enter the following formula corresponding to the topmost file's
pathname, e.g., in B3 corresponding to A3.

="='"&A3&"'!Test"

3. Fill this formula down so there's a like formula for every file,
e.g., fill B3 down into B4:B20. These formulas should produce text
strings that look like external reference formulas.

4. Select the range of formulas (B3:B20), copy, and paste special as
values on top of itself. Now you should have text CONSTANTS that look
like external reference formulas.

5. With this range still selected, run Edit Replace, replacing =
with = (yes, replacing the = character with itself). This effectively
enters all these text constants as formulas.