different sheet name in cell
different sheet name in cell
(OP)
The post about how to put a sheet name in a cell works great for the current sheet, but I can't figure out how to reference another sheet name (on sheet "A" I want cell B2 to reference the name of sheet "B")
Which part of this formula references the sheet? (if any)
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,30)
Does anyone know how to do this?
thanks!
Which part of this formula references the sheet? (if any)
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,30)
Does anyone know how to do this?
thanks!





RE: different sheet name in cell
=MID(CELL("filename",B!A1),FIND("]",CELL("filename",B!A1))+1,30)
RE: different sheet name in cell
If the header/footer values were set to display path and filename for each sheet, and the whole workbook were printed, how would it reference each sheet?
RE: different sheet name in cell
Did you try:
=cell("filename",'SheetB'!B2)
and
=MID(CELL("filename",'SheetB'!B2),FIND("]",CELL("filename",'SheetB'!B2))+1,30)
I dont' know about the header/footer question. I dont' think you can put equations into the header/footer boxes inthe page setup dialog.
RE: different sheet name in cell
RE: different sheet name in cell
Of course you have to replace the 'SheetB' with the sheet you are referencing to. So if the other sheet name is for example Test, then the formula becomes:
=CELL("filename",'Test'!B2)
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: different sheet name in cell
regards,
Kirsten
RE: different sheet name in cell
The punctuation is key. Using double quotes "name" indicates a file, whereas using single quotes 'name' designates a worksheet.
As far as the sheet name changing, the reference should update as well. Instead of typing in the reference, enter it into the equation by going to the cell and clicking it. This will ensure that the syntax is correct.
In my experience Excel almost always updates references automatically if the sheet name changes. Have you tested what happens when you change the sheet name?
This is not any different than any other worksheet cross reference, so I you are still having problems please cut/paste and poste the equation you are using so we can see what's going on.
RE: different sheet name in cell
used formula below (sheet name 'OPTION B')
=MID(CELL("filename",'OPTION B'!B2),FIND("]",CELL("filename",'OPTION B'!B2))+1,30)
thank you so much.
kza