Automatic update of Cell filename Worksheet name value from one sheet to the other

[MMM-IT]

Hello,

I have used a formula to recover the worksheet name in a cell and when switching from one sheet to the other the update is not done in the cell - is there an option I missed that will allow me to print the n pages of worksheets and have all sheets with the correct names in the sheets ?

Actually when printing the names stay the same of the last updated sheet within all sheets.

I use the formula as follows to recover the sheet name, but its not updated in the different sheets:

= RIGHT(CELL("filename"); LEN(CELL("filename")) - FIND("]";CELL("filename");1))

When selecting any sheet in the bottom the sheet name in the cell is not updated.

Same for printing:

I had expected that printing 3 different sheets will give in each sheet the actual sheet name and not in all three pages the same sheet name.

The only way to update I found is manually refresh the sheet by F9 even though the Excel help says the F9 or Shift F9 is only required if automatic update is off, but it is selected on. When printing I would have to pass through all sheets and update them manually one by one and then print one by one. 

I found another strange issue about this problem, when opening two times Excel, then the name of the second Excel File introduces itself into the cell of the other Excel File !

In the annexe I will put a screenshot showing it is switched on automatic but showing the wrong name in the cell...

What option did I miss please ?

Best regards 

Martin

PS: 

The Excel Version is:

Microsoft Excel 2019 MSO (16.0.14228.20200) 32-bit
Product ID: 00413-20019-01150-AA024

Windows Version is: Windows 10 Pro 20H2 OS build 19042.1110 Windows Feature Experience Pack 120.2212.3530.0 

[#2]

@MMM-IT 

If you’re looking to ask a question or start a conversation about Excel, you’re in the right place!

Please include the following info to help others answer your question: Click on the Link
Welcome to your Excel discussion space!

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

Hello, everyone, I have the same issue. I am trying to get file name with CELL("filename"). It is working fine, until I save any other file. Then it gives me the name of that other (just saved) file. Was this behavior expected or is it a bug?
I wanted to use date stamp from name of the file in some additional formulas. Like this, I am not able to do that.

Thank you in advance.
Lucia

Windows 10, 64 bit, Version 20H2

[#4]

@Luc123 

You have to rephrase the formula.

Example: Instead of =SUM(BE3:BE37), but = SUM(Sheet1!BE3:BE37).

If your worksheet is called "Sheet1".

There was still an old trick, I don't know if it still works. 

In the first worksheet I mark the cells that I want to transfer to another worksheet and press Ctrl-C. Then switch to the target worksheet and mark the target position by clicking in the cell.

Then right mouse button -> paste content

... Now click on the "Link" button at the bottom right in the "Paste content" selection menu.

Then the contents are there, but all cells are references to their origin in the other worksheet. 

Create or change a cell reference

A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. Click on the upper Link for more Informations.

CELL function

Note: Formulas that use CELL have language-specific argument values and will return errors if calculated using a different language version of Excel. For example, if you create a formula containing CELL while using the Czech version of Excel, that formula will return an error if the workbook is opened using the French version.  If it is important for others to open your workbook using different language versions of Excel, consider either using alternative functions or allowing others to save local copies in which they revise the CELL arguments to match their language. More informations on the upper Link.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.