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.

How can I get the sheet name in a cell? (I have more than one sheet, I want to get the current on each sheet)

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

Microsoft explains you might need to use an array formula to get the sheet name:

To display the name of the current worksheet, type the following formula as an array formula

=RIGHT(CELL("filename"),LEN(CELL("filename"))-
  MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"),
  ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))

where "filename" is literally the word "filename", in quotation marks.

NOTE: You must input this formula as an array formula. To input a formula as an array formula, press CTRL+SHIFT+ENTER.

Also note that this is very platform dependent. On a Mac, you'll need different formulas, as it uses different path separators. Also, it might work differently in OpenOffice.org and other alternatives. And when using on an unsaved workbook, you might get #VALUE! instead.

When not using an array formula, you might get the name of the workbook (the last part of its file name), like so:

To return the name of the workbook only, like used in the following formula:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
  SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
share|improve this answer
add comment

Here's a formula.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

(Link to more info)

share|improve this answer
    
Note that the reference to "A1" is not needed. Also, note that this is not platform independent, and that the formula might remain blank until you save the workbook. See also support.microsoft.com/kb/323225 –  Arjan Dec 7 '10 at 19:29
    
If I put this in two worksheets it will have the same name in both. It doesn't get the current worksheet where the cell is in. It gets the current worksheet I am viewing and replicate the value on all other fields in all other worksheets. –  BrunoLM Dec 8 '10 at 12:36
add comment

On LibreOffice, I've mixed your formula into the following, and the number 20 towards the end will be the limit on the characters that will be displayed:

=(SUBSTITUTE(MID((RIGHT(CELL("filename"),LEN(CELL("filename"))-MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"),ROW(1:255:255)))),SEARCH("\",CELL("filename"),ROW(1:255:255)))))),FIND("#$",(RIGHT(CELL("filename"),LEN(CELL("filename"))-MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"),ROW(1:255:255)))),SEARCH("\",CELL("filename"),ROW(1:255:255)))))))+2,20),1,1))

share|improve this answer
1  
This is for MS Excel. –  KronoS Jul 25 '12 at 18:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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