Have formula bar reflect the link to a cell

[SpaceBurd]

HI - think I've discovered an issue with Excel...or at least not seen/noticed it before. - when I mess about with a reference to another file on a sheet, the formula bar doesn't reflect the correct details.

Scenario:
I create a workbook and in cells A1-A3 I key a1, a2, a3. Save the workbook as FB2.xlsx
I create a workbook and in a cell I press = and point to cell a1 of FB2.xlsx and press enter
The cell shows a1
I close FB2.xlsx
In the cell with the formula I replace A1 at the end of the formula with A2 (to show that it is pointing somewhere) and replace the workbook name with garbage (see picture I'll attach)
I press Enter and Excel prompts me the location of the workbook to reference - I select FB2.xlsx and click Open
The contents of the cell update to show cell A2 of FB2.xlsx as one would expect, but the text in the formula bar don't reflect the filename - it shows the garbage I typed in as the workbook name

On the image, the top part of the image shows the link to the workbook as I would expect. The bottom part of the image shows the result of typing garbage as the workbook name and changing the reference to A2.

Is there something I can do to make it update the formula bar correctly? Formulas are set to Automatic, and F9 made no difference.

I first noticed this at work on finance spreadsheets - links had been changed but the past few months may all be showing as April 2024, for example, but are showing the correct data. If I try this via a cloud drive (OneDrive) the formula bar updates as I would expect it to.

Cheers.

 

[Jeffrey Mahoney]

I duplicated your process with FB2.xlsx saved to my documents on One Drive. Then I saved it to C:\Temp.

Each time I changed the formula to reference a different cell in FB2 it worked as expected.

Are the finance workbooks saved to Sharepoint?

Jeff

 

[SpaceBurd]

I duplicated your process with FB2.xlsx saved to my documents on One Drive. Then I saved it to C:\Temp.

Each time I changed the formula to reference a different cell in FB2 it worked as expected.

Are the finance workbooks saved to Sharepoint?

Jeff

 

[SpaceBurd]

HI - no, like in my test that I was able to reproduce this, they're on shared drive - non cloud.

 

[RoryA]

Does the formula text change if you select a different cell and then reselect the original?

 

[SpaceBurd]

No. If there's multiple cells on the sheets going between them all, clicking off and back on, the formula still shows the garbage I typed into it.

 

[Peter_SSs]

I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Firstly, I can reproduce what you have described.

Is there something I can do to make it update the formula bar correctly?

I don't know what your circumstances are for doing this but I guess an obvious suggestion is don't "replace the workbook name with garbage" in the first place.


Second option if you do have the garbage (I'm doing this with MS 365) ..

• On the Data tab click 'Workbook links'
• In the Workbook links pane at the right click the three dots to the right of the garbage name & choose 'Change source'
  
• Choose FB2.xlsx (or whatever file you want) from the 'Recent' list or else Browse to it and click 'Select'
• The formula bar should now be updated (for all cells that had that same garbage text in place of a workbook name)