different sheet name in cell

[kza]

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&quot

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!

 

[macajm]

I think you just need to put the sheet "B" reference in the formula like this:-
=MID(CELL("filename",B!A1),FIND("]",CELL("filename",B!A1))+1,30)

 

[kza]

Thanks, I tried it and it looks like excel thinks "B" is another workbook, because instead of entering a value in the cell, a directory window opens with the message: "update values: B", and it wants an existing filename.

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?

 

[bltseattle]

as noted in the other thread,

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.

 

[kza]

Thanks - and yes I have tried both, but xls thinks "B" or "B2" is another filename and opens an explorer window... still searching for this one.

 

[joerd]

kza,

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.

 

[kza]

Thanks, Joerd - that works and it makes sense, but seems to defeat the purpose of it all, because if the sheet name changes then it is not automatically updated in the cell referencing it? I guess I am trying to do something like that, but this works for now. Maybe I have to do a macro.

regards,
Kirsten

 

[bltseattle]

KZA
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.

 

[kza]

yeah!! thanks for pointing that out to me - and you are right, updating the sheet name is reflected in the cell referencing it.

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