12

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)

7

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)
7

Here's a formula.

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

(Link to more info)

  • 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
2

First save your file than enter this formula anywhere

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
  • + This does work and is short. On a side note: In german Excel use =RECHTS(ZELLE("dateiname");LÄNGE(ZELLE("dateiname"))-FINDEN("]";ZELLE("dateiname"))) – nixda Nov 18 '14 at 12:05
  • In my case I needed to use ; instead of , when separating the elements of a function – WoJ Nov 5 '15 at 14:23
0

Tested and works on M$EXCEL and LibreOffice CALC (Formula is in Spanish):

=EXTRAEB(CELDA("filename");SI(ESERROR(1+HALLAR("]";CELDA("filename")));SI(ESERROR(3+HALLAR("'#$";CELDA("filename")));0;3+HALLAR("'#$";CELDA("filename")));1+HALLAR("]";CELDA("filename")));LARGO(CELDA("filename")))

Note: It is one huge formula to fit inside other formulas or use only one cell.

I think in english: EXTRAEB would be MIDB, CELDA would be CELL, SI would be IF, ESERROR would be ISERROR, HALLAR would be FIND and, LARGO would be LENGTH.

It is much better if you split such huge formula into multiple cells (to avoid calling the same multiple times):

  1. =CELDA("filename")
  2. =1+HALLAR("]";<CellReferenceFor_1>)
  3. =3+HALLAR("'#$";<CellReferenceFor_1>)
  4. =ESERROR(<CellReferenceFor_2>;)
  5. =ESERROR(<CellReferenceFor_3>;)
  6. =EXTRAEB(<CellReferenceFor_1>;SI(<CellReferenceFor_4>;SI(<CellReferenceFor_5>;0;<CellReferenceFor_5>);<CellReferenceFor_4>);LARGO(<CellReferenceFor_1>))

Why on the hell getting sheet name is so complicated, while getting sheet position on sheet list is so easy on modern spreadsheet (warning, on EXCEL 2000 this one does not exist): =HOJA(A1)

And another thing: why functions are called different on each language, what if i have M$EXCEL is Spanish and CALC in English (not to say Russian, Chinesse, etc)? what a hard work, need to remember all function names on multiply idioms.

Internally they are saved with speific names, for example =SHEET(A1) that is the same as =HOJA(A1) is saved as =_xlfn.SHEET(A1); why not use on all idioms the last one?.

Or said in another form: why if i put it in English it say it is not vaild function name (if CALC / M$EXCEL is not on English)?

With M$ACCESS and SQL functions that does not happen, on Spanish version i can type them on Spanish ESNULO() and on English ISNULL() and it takes it correctly.

If it is too much complicated to accept both; well, then accept only one, do not localize to multiply idoms!

Well, returing to question, the trick i use on that huge formula is to test if ] exists (M$EXCEL), else test if exists '#$ (LibreOffice CALC) while extracting the name from =CELL("filename"). It is so weird !

Again with sarcastic questions: Why they added =SHEET(A1) and not =SHEETNAME(A1)? It makes to all of us harder work !

-1

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))

  • 1
    This is for MS Excel. – KronoS Jul 25 '12 at 18:08

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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