14

I want to be able to display the name of the current worksheet in a cell on that worksheet.

How can I do that without resorting to VBA scripts?

If it matters, the particular flavor of Excel I'm working with is 2007, but I would prefer something as version-agnostic as possible.

Improve this question

1 Answer 1

Reset to default
19
+100 
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

The workbook needs to be saved before this function works.

The key to the function is =CELL("filename",A1), which returns a handy string that includes the file path, the file name, and the worksheet name: C:\Users\john.smith\Desktop\[Test.xlsx]Sheet1

See:

Improve this answer
4
  • The cell reference A1 is optional, no?
    – ale
    Jan 25, 2013 at 19:58
  • 2
    This works for me: =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
    – ale
    Jan 25, 2013 at 19:59
  • 2
    You can omit the cell reference and it does not matter if you only have one Cell() function in the file. However, if you use the cell function to show the sheet name on each sheet, you need the A1 reference, otherwise each sheet will show the same value. Which value is shown depends on which formula was last recalculated. So, put the formula in Sheet1, Sheet2, Sheet3, without a reference to A1. When you recalculate Sheet3, it will show "Sheet3". Go to Sheet1 and it will also show "Sheet3". -- Probably not what you want. But with a cell reference, the formula will work as expected.
    – teylyn
    Jan 26, 2013 at 7:46
  • 6
    Slightly shorter.....=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)
    – barry houdini
    Jan 26, 2013 at 10:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.