Linking Worbooks

[potter.justin]

I was wondering if there is a way to link workbooks in excel using
cells in the filename path. In otherwords if you wanted to create a
summary sheet from multiple workbooks, would there be a way to use a
cell reference in the name instead of an explicit reference. ie:
='O:\Scope Sheet Templates\[2a - Earthwork.xls]2a'!A

and you had in your summary sheet:

Column A Column B
2a Earthwork
2b Caissons
2c Asphalt
2d Tennis Courts
2e Pavers
2f Site Concrete
2h Site Utilities

Such that you could use an entry from Column A and an entry from Column
B to reference the filename:
=CONCATENATE("'",INFO("directory"),"[",A11," ","-"," ",B11............)
such that it would actually reference the appropriate workbook? Or am
I going about this totally wrong?

Thanks

 

[Tom Ogilvy]

This isn't supported for closed workbooks.

Harlan Grove wrote a UDF that can do it.

http://tinyurl.com/rto3s