Extracting a value from another (non-active) workbook using a path dynamically formed

I can’t get Excel 2002 to resolve an external reference to a value in another workbook.

If I have the following literal expression in a cell in my active worksheet, it works:
='C:\Documents and Settings\My Documents\HCS\SAC123.xls'!NumSAC

In this case, the contents of the variable NumSAC is copied into the active workbook from another workbook named SAC123. This works fine without the SAC123.xls workbook being active.

But what I really need to do is have Excel reference an expression that I form by concatenating together strings and a cell value (from the active worksheet).

The following expression evaluates to be exactly the literal expression above (which worked), but it doesn’t act as a valid reference.
=”'C:\Documents and Settings\My Documents\HCS\" & $B12 & ".xls'!NumSAC"
The result displayed in that cell is
'C:\Documents and Settings\My Documents\HCS\SAC123.xls'!NumSAC
and not what I want.

If I try to use the INDIRECT function with the above character string, like this,
=INDIRECT(”'C:\Documents and Settings\My Documents\HCS\" & $B12 & ".xls'!NumSAC")
then I get
#REF!

However, if I open the SAC123 workbook (so that it is in the ring of active workbooks), then I get the correct value put into the cell.

The INDIRECT function only works if the referenced workbook is active.

Is there a way of having Excel evaluate a character string as a valid reference without having to have the referenced workbook(s) active?

In my case, I want to have a master active workbook that references over 200 other workbooks.

If it helps at all, the master workbook will be in the same file folder as the 200 other workbooks.


Thanks for any suggestions you may have.

You can't use INDIRECT with closed workbooks.

MoreFunc, a popular add-in, has a function INDIRECT.EXT that does.

You would need to add a free addin from here called Morefunc.xll

http://download.cnet.com/Morefunc/30...-10423159.html

and then use function INDIRECT.EXT in place of your current INDIRECT

Morefunc.xll has a special feature that allows you to embed the addin into the workbook so that you can share the file with others without having them install the addin.