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.


"Earl Kiosterud" wrote in message
...
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.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Eric" wrote in message
...
You are right! It's working...

Thank you very much.

Eric.


"Frank Kabel" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany

"Eric" schrieb im Newsbeitrag
...
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" wrote in message
...
Hi
use INDIRECT for this.

--
Regards
Frank Kabel
Frankfurt, Germany

"Eric" schrieb im Newsbeitrag
...
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.