Inputting Sheet Names

K

kyleboyd

New Member
Joined
Feb 22, 2011
Messages
15
I am not a complete newbie to Excel, but so far what I am trying to do seems to be a few cuts above my pay grade:

I have a document with many sheets that are often added or deleted. I have a master sheet that contains the names of each sheet. What I would like to do is reference cells on different sheets without manually linking each reference.

Column A
Sheet 1
Sheet 2
Sheet 3
etc.

Column B
'Sheet1'!B1
'Sheet2'!B1
'Sheet3'!B1
etc.

Hope that makes sense, any help would be greatly appreciated!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Awesome, it works! Thanks for the quick reply!

Is there any way to copy a blank cell - as it is now if the original cell is blank it returns a zero. Thanks!
 
Upvote 0
Try

=IF(INDIRECT("'"&A1&"'!B1")="","",INDIRECT("'"&A1&"'!B1"))

try to avoid too many of these - they are volatile formulas that could slow your sheet down.
 
Upvote 0
My method just returns text which is "unformatable" (the technical term). I am resorting to using conditional formatting to hide zero's unless someone has a better idea. Now I am wondering if there is any possibilities of copying formatting along with the contents of the cell? Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA Help - unless something else works
2
Replies
17
Views
569
Joe4
Joe4
P
  • Question Question
Document Status Screen
Replies
0
Views
89
Pestomania
P
S
  • Question Question
Sheet Name before NAMED RANGE
Replies
3
Views
233
Jeffrey Mahoney
Jeffrey Mahoney
E
  • Question Question
Mass Sheet Update
Replies
3
Views
157
mumps
mumps
J
  • Question Question
Pulling Data From ~300 Individual Sheets Onto a Master Sheet
Replies
1
Views
192
ranman256
ranman256

Forum statistics

Threads
1,224,583
Messages
6,179,673
Members
452,937
Latest member
Bhg1984

Share this page

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
Back
Top