Formula variable

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
Check out the INDIRECT function.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
If I understand your example correctly, try:
=INDIRECT(C2&"!A1")
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

Well-known Member
Joined
Oct 16, 2002
Messages
657
Sorry, That does not seem to work

Receive Ref!

Correction Barry's works, Thanks
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

Well-known Member
Joined
Oct 16, 2002
Messages
657
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 16, 2002
Messages
657
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
 

Forum statistics

Threads
1,136,354
Messages
5,675,301
Members
419,560
Latest member
g3org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top