indirect reference
 

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.
--
Boris

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

file name in A1 and sheet name in B1

--
Regards,

Peo Sjoblom

(No private emails please)


"BorisS" wrote in message
...
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.
--
Boris

Hi


"Peo Sjoblom" wrote in message
...
=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)


"BorisS" wrote in message
...
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.
--
Boris

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


"Arvi Laanemets" wrote:

Hi


"Peo Sjoblom" wrote in message
...
=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)


"BorisS" wrote in message
...
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.
--
Boris