Variable sheet name in formula

[Eric]

Hello,

I dont know if this is possible, but I am trying to build a formula which
contains the name of a sheet that will change every day.
In fact, I would like a cell to contain the name of the sheet and then
reference the content of that cell in my formula.

Ex: sheet2.A3 would contain the text "Sheet2"
my formula would look like = sheet2.A3!B4*C45........., where sheet2.A3!B4
would be cell B4 of sheet2.


Thank you.

Eric.

 

[Frank Kabel]

Hi
use INDIRECT for this.

 

[Eric]

Frank,

INDIRECT seems to work when you want to reference a cell but not for just
the sheet name.
I want to be able to create a variable that contains the name of a sheet and
then use that variable in a formula.

Thank you.

Eric.

 

[Frank Kabel]

Hi
it will work also for the sheet name. e.g.
A1: sheet1
A2: X1
A3: Formula
=INDIRECT("'" & A1 & "'!" & A2)

this will get the value from sheet1!X1

 

[Eric]

You are right! It's working...

Thank you very much.

Eric.

 

[Earl Kiosterud]

Eric,

If the cell will always be B4, as implied in your original post (you seem to
have made up your own syntax for it <g>), you need not store it in a cell.
Changing Frank's solution slightly:

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

The quote marks around B4 are necessary, or else it will look in B4 for a
cell address.

In the year 2028, Excel will give us an INDIRECT function that will allow us
to just supply a sheet name without having to drum up all those
hieroglyphics.

 

[Eric]

Earl, thank you for your input.

B4 was just an example. In fact the cell reference varies too, it is the
result of a specific formula...

Eric.