Displaying Sheet Name in a Cell

[EER]

Is there a command or macro to display the name of a sheet in a cell?

I know how to do it in a header or footer, but not in a cell.

I need to copy the name that appears at the bottom of a spreadsheet in a cell at the top -- no need to reference the name of a different sheet, just the same one the cell is on.

Any ideas?

 

[Domski]

Maybe as a formula try: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

File must be saved to work.

Dom

 

[EER]

Perfect, thanks!

 

[EER]

Oops, spoke too soon.

I forgot to mention I have multiple sheets in the workbook, and the same name is displaying on each sheet when I paste the formula in and update the field....

 

[Domski]

I've never had that before. Tested it in 2003 and 2007 and it returns the sheet name of the sheet that it's on for me.

Dom

 

[EER]

Here is what I am doing to produce the error:

o. Paste the formula in a cell, it works fine.
o. Paste the formula in a cell in another sheet, it works fine.
o. Paste the formula in a cell in another sheet, it works fine.
o. <ctrl>S to save the workbook, all cells now display the same name.

 

[tusharm]

Use =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Omitting the cell reference from the CELL function causes it to use the active cell.

Oops, spoke too soon.

I forgot to mention I have multiple sheets in the workbook, and the same name is displaying on each sheet when I paste the formula in and update the field....

 

[Domski]

I'd never noticed that. It seems to change each one to the active sheets sheet name.

I think it should change to show correctly next time the sheet calculates. You could either do this manually (press F9) or maybe add some code to each sheet to calculate the sheet when it's activated.

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

Dom

 

[Domski]

Use =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Omitting the cell reference from the CELL function causes it to use the active cell.

Doh!!! Didn't think I'd had that problem before.

 

[tusharm]

Additionally, the CELL function is one where Excel allows a reference to the cell containing the formula w/o grumbling about circular references. So, if the formula is entered in H7, it would be =RIGHT(CELL("filename",H7),LEN(CELL("filename",H7))-FIND("]",CELL("filename",H7)))

This way the reference will never become invalid (becaue of a row/column/range deletion for example).

Doh!!! Didn't think I'd had that problem before.