indirect reference

B

BorisS

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

Peo Sjoblom

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

file name in A1 and sheet name in B1

--
Regards,

Peo Sjoblom

(No private emails please)
 
A

Arvi Laanemets

Hi


Peo Sjoblom said:
=INDIRECT("'["&A1&"]"&B1&"'!B2")

file name in A1 and sheet name in B1
Click to expand...

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


Arvi Laanemets
 
B

BorisS

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 said:
Hi


Peo Sjoblom said:
=INDIRECT("'["&A1&"]"&B1&"'!B2")

file name in A1 and sheet name in B1
Click to expand...

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)
Click to expand...
Click to expand...
 
Top