Inputting Sheet Names

[kyleboyd]

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!

 

[VoG]

Try

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

 

[kyleboyd]

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!

 

[VoG]

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.

 

[kyleboyd]

Ah. Well that could be problematic, my workbook will be full of them. Any other options?

 

[kyleboyd]

How about this:

=MID(INDIRECT("'"&A1&"'!A3"),1,255)

 

[kyleboyd]

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!