Combining cell Ref into formulae

[Darin Kramer]

Hi There

Sheet 1 has formulae in cell a1 that says "=sheet2!b2"
Thus value in cell b2 in sheet2 is displayed in a1 in sheet1

Problem I need to change sheet2 to sheet 3 sheet 4 etc etc, so I wanted
to make the text "sheet2" within the formulae above relative, ie
instead of saying "sheet2!b2" I wanted to refer Excel to a cell Say Z1
and then combine it with b2. (sortof like ="z1!&b2" - but this doesnt
work)
In Z1 I would then type Sheet2 or whatever I wanted, it would make
duplicating it much easier.

any ideas welcomed (hope its not all too confusing...

Thanks

D

 

[Bob Phillips]

=INDIRECT(Z1&"!B2")

--

HTH

RP
(remove nothere from the email address if mailing direct)