Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 10 down vote accepted
+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:

share|improve this answer
    
The cell reference A1 is optional, no? –  Al E. Jan 25 '13 at 19:58
2  
This works for me: =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))) –  Al E. Jan 25 '13 at 19:59
1  
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 '13 at 7:46
4  
Slightly shorter.....=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) –  barry houdini Jan 26 '13 at 10:23
    
Thanks for the explanation, @teylyn. –  Al E. Jan 27 '13 at 18:46

protected by Community Jun 18 at 22:23

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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