Ask Your Question
1

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

asked 2016-06-04 21:18:02 +0200

BearDrummer@Gmail.com gravatar image

updated 2018-07-25 22:31:11 +0200

erAck gravatar image

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.

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
2

answered 2016-06-05 07:08:02 +0200

pierre-yves samyn gravatar image

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

edit flag offensive delete link more
1

answered 2016-06-05 00:27:27 +0200

Lupp gravatar image

updated 2018-07-25 23:23:58 +0200

There is no standard function in Calc returning the name of the (a) sheet.
Workarounds:
1. (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.
2. 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.

edit flag offensive delete link more
1

answered 2016-06-04 23:38:44 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more
0

answered 2020-11-14 21:31:43 +0200

kamlan gravatar image

updated 2020-11-14 22:29:20 +0200

There are two sheets in the example C:\fakepath\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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2016-06-04 21:18:02 +0200

Seen: 15,649 times

Last updated: Nov 14 '20