Excel 365 after Save As - calculation not working automatically until a change is detected

HI all

I have two excel 365 files, one works and one doesn't - no obvious difference, both set to automatic calculation with iterative calculation ticked

In both files I have a formula returning the filename and this triggers a different heading on the worksheet depending on the filename

In the one that works: Click File then Save As and give it a different file name and the save as file appears and immediate recalculation works and the filename immediately updates and the heading is correct

In the one that doesn't work - click file and Save As and give it a different filename and the save as file appears but immediate re-calculation does not work and the original filename is still there and the heading is not correct.
If you press F9 or edit and change a cell the proper filename appears and all is well

My problem is that when I save as I want it to be immediately obvious whch file you are in.

It works fine with the first one and doesn't word with the second one and I can't see any difference between them - rather frustrating. Any help would be much appreciated.

Kind regards

Roy

Hi Richard

Sorry I was hoping for an easy solution.

When I simplified my working file to send it now does not work either so I'm attaching it here.

Two files:

Calculation works

Calculation works copy - while the original file worked this is a copied sheet from it and now it doesn't work

The procedure is

Open Calculation Works and you see my formula to display the filename

Then save as calculation works copy

You will see that the filename does not change until you press F9

On my original file when you created the copy file it immediately changes to display the new filename and the heading changes

Hope this is sufficient to explain my problem.

Although my orignal file works and these don't my query really is that when you "save as" why does the copy file not re-calculate immediately.

Kind regards

Roy

Hi Richard

I have been doing a bit of Google-ing and found that the formula I used displays the filename last saved? and not necessarily the file containing the formula. I have now substituted the formula =cell(filename",a1) and it gives me the full path and filename of the file the formula exists in. When saving as the copy file it still displays the original filename until i press F9 or opening the original file as well as the copy file seems also to trigger a calculation and it then displays the proper filename.
So i'm not really any further on except i have a better formula. The fault seems to lie with when saving as and the copy file appears it does not calculate and immediately display the correct filename. I suppose you could live with that except I have another file which does work although why is a mystery. It was used to create the file I uploaded but then it did not work for some unknown reason. Arrrgh! Roy