Using contents of a cell as the sheet name in another formula

[sbird]

I am trying to use the contents of a cell as the sheet name in another formula.

A B
1 Res A ='Res A'!C4
2 Res B ='Res B'!C4

Right now, 'Res A' and 'Res B' in column B are hard coded. I want them to
refer to the text in column A so when I copy the formulas down, the
references are updated automatically instead of having to do it manually.
Once we make that work, the equation I really need to embed the variable
sheet names in is a little more complicated, and looks like this:

B C
4 Res A =INDEX('Res A'!$A$1:$AZ$256, MATCH($A$1,'Res
A'!$A$1:$A$256,),
MATCH(C$1,'Res A'!$A$1:$AZ$1,))

Where all 3 instances of 'Res A' need to be replaced by a variable expression.

 

[Gary''s Student]

Instead of:
='Res A'!C4
use
=Indirect(A1 & "!C4")

You can change the content of A1 and the sheet reference will also change.

 

[Dave Peterson]

Since you have spaces in the worksheet name, you could use:

=indirect("'" & a1 & "'!c4")