[Solved] Get sheet name with cell formula

[AWoodShed]

I've read the various previous threads regarding this same question, but I just can't get my head around any of them.

Apologies if I should NOT be asking this again, but I'm completely stumped, and my query is a little different to the others.

I have a spreadsheet with multiple tabs.

In it's simplest form, I want to set a formula in a cell, that uses the current worksheet name.
I don't need references to particular cells within the sheet, JUST the worksheet name itself.

Example: Sheet called "Trade"
Within that sheet, Cell D1 = "Trade"
Simple as that.

I need to be able to change the worksheet name, and it instantly changes the cells that reference to the name as a string.
Each worksheet has about 4000 rows of data - and SOME of the cells in Column D in each sheet require the sheet-name (as text string), and others do not.
So when I need to rename the sheet (it's an occasional operational requirement of what I'm doing), I don't want to have to find/replace the old sheet-name with the new one, in each sheet every time this change is required.
I really just want to rename the sheet-name variable once, and it auto-applies to all other references to it.

It really seems like such a simple and obvious thing, I can't believe an advanced spreadsheet platform doesn't have this as a basic function.

I have tried using "=SHEET()" in the cell, but this only gives the sheet number, not it's text string name.
I'm not sure how to convert the sheet number, into it's text-string.
If I have to use an Index/Lookup query on a helper-sheet, then so be it... but I'd rather a simpler solution.

I have no understanding from previous threads on here, how to use the INDIRECT() or ADDRESS() functions, and Google searching hasn't assisted that either.
The in-depth responses from @Villeroy and others, for example, are always hugely appreciated, but for these functions, their responses have gone straight over my head.

[RusselB]

A quick look at the functions that are built into Calc doesn't show anything that would return what you appear to be wanting.
That said, Villeroy's code in viewtopic.php?f=9&t=104616&p=507122&hil ... e+#p507122 seems to be as simple as one can get for a custom function.

[AWoodShed]

Thanks @RussellB

I'll give that a go.

I've kind of succeeded what I need using a different macro provided in another thread, but I had to use it with a helper-sheet, then cross-reference against that with an Index/Match function call, but I'd really like an easier solution, if it's possible.

[robleyd]

[Solved] Variable Sheet Reference seems to be the same problem.

[RusselB]

It appears to be similar, but it's my understanding that the OP would like to be able to set/change the sheet name and then have any relevant references update to reference the new/updated sheet name.

[robleyd]

I really just want to rename the sheet-name variable once, and it auto-applies to all other references to it.

I understood this as sheet name being stored in a variable, or cell. Obviously, this would involve two steps; [one] rename the sheet and [two] copy or type the sheet name into the cell used in formulae elsewhere. May be simpler than mucking about with the evil macros

[pharmankur]

It is done with custom function SheetNameByNumber and formula
Attached a file test.ods to demonstrate the same.

Macro used is -->

Code: Select all

REM  *****  BASIC  *****

Function SheetNameByNumber(Optional pNo As Long) 
'There seems not to exist a StandardFunction for this purpose!
'Note: A cell will NOT RECALCULATE DUE TO A CHANGE OF THE SheetName!
'You will have to FORCE RECALCULATION "on any event".
'This can be done by appending "+NOW()*0" to the parameter expression, e.g.
Dim oDoc As Object
Dim oSheet As Object
Dim nSheets As Long

	IF IsMissing(pNo) THEN
		SheetNameByNumber = ".Parameter.Missing."
		Exit Function
	ENDif 
oDoc = ThisComponent
nSheets = oDoc.Sheets.Count
	IF (pNo > 0) AND (pNo <= nSheets) THEN
		oSheet = oDoc.Sheets(pNo-1)
		SheetNameByNumber = oSheet.GetName
	ELSE SheetNameByNumber = ".No.Result."
	ENDif
End Function REM SheetNameByNumber

[MrProgrammer]

In it's simplest form, I want to set a formula in a cell, that uses the current worksheet name. … I need to be able to change the worksheet name, and it instantly changes the cells that reference to the name as a string.

This attachment uses only normal functions (no evil macros) and avoids the need to use NOW() to force recalculation.
Formula =MySheet returns the sheet name as a string. It changes if you rename the sheet.

202202251300.ods

(9.29 KiB) Downloaded 330 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[AWoodShed]

Thanks all for the replies.

I will give each solution offered a try. I think @MrProgrammer's solution may be the best at this point, as I don't have much experience working with macros and I know there can be security risks associated with them.
I'm unsure if my employer will allow me to use them in my work environment, so I will try those at home.

Sincere thanks again to all

[Villeroy]

A most simple userdefined function:

Code: Select all

Function SHEETNAMES()
SHEETNAMES = ThisComponent.Sheets.getElementNames()
End Function

=INDEX(SHEETNAMES(SHEET())) returns this sheet's name.
=INDEX(SHEETNAMES(1)) returns the first sheet's name.

Disadvantage: Requires enforced recalculation [Ctrl+Shift+F9] after renaming a sheet.

[AWoodShed]

Thanks @Villaroy

I gave @MrProgrammer's solution a try, and it worked perfectly.

I'll give yours a go as well, for occasions where instant-recalculation is not required.

Incidentally - where do I "code" the Function that you've provided in your example?
I haven't used user-defined Functions before (or Names, as required in @MrProgrammer's solution, for that matter), so I'm learning as I go

[Johann-Tree]

A most simple userdefined function:

Code: Select all

Function SHEETNAMES()
SHEETNAMES = ThisComponent.Sheets.getElementNames()
End Function

=INDEX(SHEETNAMES(SHEET())) returns this sheet's name.

I could it only got to work with following formula:

Code: Select all

=INDEX(SHEETNAMES();SHEET())

Otherwise not the current sheet's name but the name of the first sheet will be shown.

Is there a way to put the logic of index() and sheet() into the function?

[Alex1]

This attachment uses only normal functions (no evil macros) and avoids the need to use NOW() to force recalculation.
Formula =MySheet returns the sheet name as a string. It changes if you rename the sheet.

This solution depends on the length of the word SHEET, which may be different in other language versions.
One can search for the position of the dollar sign and start copying there, or use the length of another formula containing the word SHEET.