Formula variable

[Chas17]

On Sheet1 I have a formula

=Sheet2!A1

how do I place a formula in a cell so that the sheet name, i.e Sheet2, is located in a cell. In other words i would like to place "Sheet2" in Cell A2 on Sheet1 and have the formula something like

=c2 & "!A1 vice
=Sheet2!A1

 

[Joe4]

Check out the INDIRECT function.

 

[Chas17]

Tried that, cannot seem to get it right

 

[Joe4]

If I understand your example correctly, try:
=INDIRECT(C2&"!A1")

 

[barry houdini]

If sheet name is in C2 then to refer to cell A1 on that sheet

=INDIRECT("'"&C2&"'!A1")

which would cope with sheet names which contain spaces

 

[Chas17]

Sorry, That does not seem to work

Receive Ref!

Correction Barry's works, Thanks

 

[just_jon]

Keep in mind that while single word sheet names can be entered as they appear -- Sheet1, for instannce -- sheets whose names are 1+ words need single quotes -- 'Sheet One' -- in referencing syntax.

 

[Chas17]

thanks Jon,

I originally had jm14's response but my sheet name was more than one word. Thanks again to all jm, barry, jon.

Chas

 

[Joe4]

I originally had jm14's response but my sheet name was more than one word. Thanks again to all jm, barry, jon.

It exemplifies the danger in oversimplifying your examples (your example only had one word, and my code works on that).

Sometimes, its those little things that will catch you! :wink:

 

[Chas17]

jm

You are correct, I should have used my actaul example instead of simplifying. Thanks for taking all the time to reply, much appriciated.

Sorry about leading you astray.

Chas