File Name Help

[kyleboyd]

I have two seperate workbooks that I need to link. I would like to be able to link using the sheet names of the first workbook.


Workbook 1, with sheets
Sheet001
Sheet002

Workbook 2, titled Sheet001.xls

So I would like to have in a cell Sheet001 something that links like so:
C:/"SheetName.xls"!A1 etc.

This is important because the individual workbooks are being manipulated, changed and added too, and I would prefer to not have to link every new file manually.

The closest I have found is somehow to use INDIRECT, but I cannot get it to work correctly.

I'm not sure how better to describe it than that. TIA

 

[texasalynn]

indirect does not work with closed workbooks. Is that the problem you are having?

 

[kyleboyd]

That probably IS a problem, however I haven't gotten there yet because I haven't made much progress. Basically is there any way to link to another workbook without directly linking?

ie

A1
C:/Path/

B1
filename.xls

C1
Sheet 1

E1
!A4

F1
=C:/Path/filename.xls/Sheet 1!A4

or

"A1""B1""C1""D1""E1"

Is that easier?