Update of Calc Sheetname not updating filename

[mward3922]

I am using the following to fill a cell in my sheet with the Sheet Name

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

If I paste this into a cell then the cell contains the Sheet Name.

However, if I then update the Sheet Name via the Sheet Tab, the Cell referenced above does not update the filename to match.

I have to re-paste the formula into the cell for it to update

I have even saved the sheet but that doesn’t force the update.

Has anybody seen a solution to this issue?

[mward3922]

Just an update:-

This issue still arises if you set the cell with Insert - Field - Sheet Name

[EarnestAl]

Click Data > Calculate > Calculate hard Ctrl+Shift+F9

[Zizi64]

You can try thinking backwards:
The Cell Functions will be re-calculated automatically, when a passed parameter is changed. Use a Custom cell function (a Macro) to rename the Sheet based the content a Cell.
Here is a Sample file with embedded (really simple) macro code. See the two existing Sheets.
You can to complete the code to eliminate the error messages at opening the file (because the macro run earlier than the CurrentController.ActiveSheet is present).

SetSheetName.ods (9.7 KB)

[mikekaganski]

Modifying anything in the spreadsheet from within a user spreadsheet function is bad, and must not be suggested/advertised. It may break at any time. The only thing that such a function must do is accept arguments, and calculate and return a value.

The rename of a sheet can definitely be made to update all instances of CELL function, so it may be considered a bug; filing and fixing that would eliminate the need for conceptually wrong workarounds.

[karolus]

Hello
instead an UDF ( as suggested by @Zizi64 ) you may include a volatile function which will trigger the recalculation eg:

=REGEX(CELL("filename");"^.+?#\$(.*)$";"$1") & T(NOW())