Using a Cell Value to reference a Worksheet

[Guest]

Hi,

I have 5 worksheets, labelled A-E.

Is it possible to build a formula which references the same cell in any one
of these worksheets by have A-E in cells A1, meaning that if I cahange the
value in A1 to either of the 5 letters, the result reflects the value in that
specific worksheet

cheers
pepperds

 

[JW]

This will use the value in A1 to establish which sheet to get the
value from.
=INDIRECT("Sheet" & A1 & "!A2")

In effect, if A1 contains letter A, then this is saying =SheetA!A2.

 

[papou]

Hello pepperds
Use INDIRECT to build your reference eg:
=INDIRECT(A1 & "!A1")
Will return value from range A1 of specified sheet in A1

HTH
Cordially
Pascal

 

[Dave Peterson]

I'd use:
=indirect("'" & a1 & "'!x99")
to return the value from X99 on the sheet named in A1.

The single quotes aren't always necessary--but won't hurt if they're there.