modular addressing

[chronotonFlux]

I want to pull information out of a certain file/tab/cell

if I type in the full [file.xls]tab!cell i get exactly what I wanted, but what I need to have it do is give me that same information after I've put together file/tab/cell as cells

What information I need:
='[file.xls]tab'!$D$5

But I need to do it this way

A1 = '[file.xls]tab'!
B1 = A1 AND D5

How do I simply break the above in two parts so that the cell call can be independant of the file call, but still return the values expected?

 

[Jonmo1]

Indirect can do that, as long as file.xls is open. It won't work if the file.xls book is closed.
Take the ' and ! out of A1 (we'll enter them in the formula instead)
so A1 = [file.xls]tab

=INDIRECT("'" & A1 & "'!D5")

 

[chronotonFlux]

Is there any way to do it without opening the file? an alternative to INDIRECT

 

[Jonmo1]

There's a 3rd party addin called MoreFunc that has a function similar to indirect and will work on closed books.
See the Recommended Addins thread

Now bear in mind with the book closed, you'll have to include the file path
A1 would be C:\SomeFolder\Path\etc\[file.xls]tab