indirect reference

[Guest]

I want to have two cells where one tells me the name of a file, and the other
tells me the name of a sheet tab in that file. I would like to be able to
use those two values in a cell to complete a reference to a cell (let's say
B2 of that file's tab). What is the formatting for this?

Thx.

 

[Peo Sjoblom]

=INDIRECT("'["&A1&"]"&B1&"'!B2")

file name in A1 and sheet name in B1

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Arvi Laanemets]

Hi

=INDIRECT("'["&A1&"]"&B1&"'!B2")

file name in A1 and sheet name in B1

But... the file you are referring to must be opened - otherwise the formula
returns an error!


Arvi Laanemets

 

[Guest]

is there a way that the error can be avoided? Short of converting to values,
of course. I'd like these to be similar to other links, where even if I
don't update them when I open the file (and the source files are closed), the
previous values still show.

Thx if you can help out.
--
Boris

Hi

=INDIRECT("'["&A1&"]"&B1&"'!B2")

file name in A1 and sheet name in B1

But... the file you are referring to must be opened - otherwise the formula
returns an error!


Arvi Laanemets

--
Regards,

Peo Sjoblom

(No private emails please)