How do you get a sheet name to display in a cell?

BearDrummer · June 4, 2016, 7:18pm

I want to have A1 display the sheet name. On the first sheet, where the sheet tab says “Mammot”, I want Mammot to display in A1. When I copy the page and paste it into the next page, which says “Potbelly”, I want A1 to display Potbelly. I am creating a feeding value chart for a tribe member, and that would prevent a great deal of headache.

PYS · June 5, 2016, 5:08am

Hi

Since 3.6 you can insert a field Sheet Name in a cell:

Activate the cell to be in Edit mode (e.g. Double-click in the cell) ,
Right-click on the cell, and select Insert Fields in the context menu.

Limitation: update not automatic, needs Shift+Ctrl+F9 if you rename a sheet (or updated on reload of course).

Regards

mariosv · June 4, 2016, 9:38pm

=MID(CELL(“filename”);FIND("#$";CELL(“filename”))+2;LEN(CELL(“filename”)))

Lupp · June 4, 2016, 10:27pm

There is no standard function in Calc returning the name of the (a) sheet.
Workarounds:

~~(Only applicable if the documet was already saved as a file)~~ Use =CELL("FILENAME";A1) in A1. The resulting text will contain the sheetname preceeded by “#”, and you can extract the sheetname using
=MID(A1;FIND("#";A1)+1;65535) e.g.
Code a user function for the purpose. In BASIC this should work:

    Function sheetName(pSheetNum)
    REM In Calc Sheets are numbered starting with 1
    REM The API starts numbering with 0.
    sheetName = ThisComponent.Sheets(pSheetNum-1).Name
    End Function

=SHEETNAME(SHEET()) will then return the name of the current sheet.

brockp · August 12, 2022, 4:45am

This solution works quite well in LibreOffice Calc version 7.1.3.2 on CentOS 7.9.2009.

KamilLanda · November 14, 2020, 8:31pm

There are two sheets in the example hello-dreams.ods, “Hello” and “dreams”, rename the “Hello”.
If you need put sheet name to other sheets and not to the current sheet, you can use two variants. But to the current sheet: first always put $A$10 to the cell, second put nothing.

=REGEX(CELL("Address";$Hello.$A$1) & 0*RAND();"\$'?(.+?)'?\.\$A\$1.*";"$1")

=REGEX(CELL("Address";$Hello.$A$1) & RAND();"\$?'?(.*?)'?\.?\$A\$1.*";"$1")

If you need put sheet name to the cell also in the current sheet, you can use IF for testing what the CELL(“Address”) returns. If it returns $A$10 then you are in the current sheet and you can use formula (MID(CELL(“filename” …). Else you can use formula from first variant. But remember you must write name of the sheet to two places in the formula.

=IF(CELL("Address";$Hello.$A$1) & 0*RAND()="$A$10";MID(CELL("filename");FIND("#$";CELL("filename"))+2;LEN(CELL("filename")));REGEX(CELL("Address";$Hello.$A$1);"\$'?(.+?)'?\.\$A\$1.*";"$1"))

Automatic recalculate you can do with function RAND.

Lupp · August 12, 2022, 11:16am

NOW() should be slightly faster when used as “volatilizer”.

readingjohn · June 25, 2021, 2:22pm

Slightly more succinct than the above, using REGEX:
=REGEX(CELL(“Filename”),"[^#]*…","")

Lupp · August 12, 2022, 1:03pm

As presented here your formula can’t work. Please use the tool (icon looking like </> ) for Preformatted text when inserting formulas in this “editor”, and make sure that no quotes (whether single or double) or apostrophes were replaced by “typographic versions”…

Even a bit more concise: =REGEX(CELL("FILENAME");"(?<=#\$).*";;1) should do.
However, all the solutions given in this thread return the actual sheetname while you may need the escaped sheetname in some cases if you chose inappropriate (but unfortunately tolerated - even if containg “#” or “’”) “names” for sheets.

BTW: Prefer the semicolon as the parameter delimiter when posting formulas here. It is still globally accepted while the comma in this role conflicts with the decimal separator used by a majority of locales.

To be able to also get the escaped sheetname if needed the following code can help. It must be placed in a module supporting VBA.

Option VBAsupport 1

Function alternativeSheetName(pVBArange, Optional pMode) As String
alternativeSheetName = ":err:"
On Local error Goto fail
If IsMissing(pMode) Then pMode = "a" REM Actual Name; parameter is not case-sensitive.
If pMode=0 Then pMode = "a" REM Calc passes a 0 for a paramter missing on a not missing position.
pMode = Left(Ucase(pMode), 1) REM Only the first place regarded.
Select Case pMode
 Case "A"
  alternativeSheetName = pVBArange.CellRange.Spreadsheet.Name
 Case "E"
  loc1 = pVBArange.CellRange.AbsoluteName
  loc2 = Split(loc1, ".")
  Redim Preserve loc2(Ubound(loc2) - 1)
  alternativeSheetName = Mid(Join(loc2), 2, 65535)
End Select
fail:
End Function