Indirect cell reference

[dtutton]

Im trying to refer to data in another spreadsheet indirectly.

i.e. in Book2, Sheet 1, A1 I have the text "[Book1.xls]Sheet1!D12"

I want to return the contents of this refered to cell to say Book2, Sheet 1, A2.

How can I do this without making a direct reference. i.e. I want it to be indirect ?

 

[AlaskanDad]

Please help me understand.

You want the cell: [Book1.xls]Sheet1!D12 to have the text value of "[Book2.xls]Sheet1!A2" without having to type the text in that cell? Are you looking for a formula that you type into cell D12 that would return your wanted text?

How does cell D12 know that anything is referencing it?

Are you looking for some slick VBA code that will look over every cell in search of cell addresses?

 

[acron]

You need to compose a string value representing the full address of the range you want to reference.

If you have the workbook name in A1, the Sheet name in B1 and the Cell address in C1 (all as text) you could use :

=INDIRECT("["&A1&"]"&B1&"!"&C1)

to get the value you are interested in. Or you could compose the string with literal values.

A.C.

 

[AlaskanDad]

After reading acron's take, maybe you're just looking for the following formula in cell A2:

=Indirect(A1)

 

[dtutton]

Thanks - the indirect command is what I was looking for. However, it does not seem to work if the workbook refered to is closed or in a different directory path to the calling sheet. I guess for this one has to go to vba ?

Thanks DT