Using a worksheet name in a formula

[Guest]

Hi,

I have 100+ worksheets. I have formulas that hard code those worksheet names.
Such as the the simple formula: =Sheet58!$E$2

I would like the "58" to be generated from cell (i.e. A1 has a value of 58)
so I can have a formula that like:

="Sheet($A$1)"!$E$2

What is the correct syntax? I tried INDIRECT, but could not get it to work
...

 

[David McRitchie]

Hi Kevin,


INDIRECT Worksheet Function
http://www.mvps.org/dmcritchie/excel/indirect.htm
and
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm


=INDIRECT($A$1 & "!$E$2)

 

[Guest]

Hi Kevin,
Try:
=INDIRECT("Sheet"&$A$1&"!$E$2")

 

[Guest]

Cool, that worked! Thx!

Now, my next issue. The formula you provided;

=INDIRECT("Sheet"&$A$61&"!$C$2")

still has the cell reference "A61" so I have to hard code the "61." Can I
reference the "A" column for the current row? Therefore, I don't have to
type the row number?

 

[David McRitchie]

Hi Kevin,

=ROW() will provide the cell's row number
=COLUMN() will provide the cell's column number

 

[Guest]

Hi David, Thx for your patience . . . .

I did not state my question clearly. In the following formula,

=INDIRECT("Sheet"&$A$61&"!$C$2")

I want to replace the $A$61 with value in the A column for that row.

Is there a function that will return the value of a specific cell on the
current row?

Example:
Cell A61 contains the value 53.

I want the INDIRECT function to lookup the "Sheet53" without having to put
$A$61 in the formula.

Thx!
Kevin

 

[David McRitchie]

Hi Kevin,
Your goal is to produce a string that looks like
sheet53!$C$2
and to place that formula adjusted to it's new location
as the argument of INDIRECT.

cell A61: 53
test G61: ="'sheet" & OFFSET(G61,0,1-COLUMN()) & "'!$C$2"
cell H61: =INDIRECT("'sheet" & OFFSET(H61,0,1-COLUMN()) & "'!$C$2" )

The G61 and H61 internal are the address of the cell itself