why is excel not referencing cells in another worksheet

Question

I have an issue that should be so simple to fix that I feel a bit stupid for even asking, but for the life of me, I cannot find a fix. All I want to do is reference a text cell in a second worksheet and bring the value back to a cell in the first worksheet. This is something I have done more days than I have been around and of course, up until now, it has always worked. Instead of bringing back the text value it simply brings back the worksheet and cell reference, eg Sheet2!C27. This is only happening with a few cells in the first worksheet and many overs work as they should. I have also created a new (third) worksheet and created references to the cells in the second worksheet that will not copy into the first and that works fine indicating that there is no issue with the second sheet. The formatting of all cells is the same and I have also tried, in the first worksheet, copying the rows which contain the cells into which the text does copy into and inserting them under the rows with the 'faulty' cells but they then inherit the issue of the row above. Can anyone shed some light on what the issue may be?

Answer 1

There appears to be a significant glitch in MS Excel that has been introduced with one of the recent updates. I am using the "=" function to reference a cell in one worksheet for use in a second worksheet (all within the same workbook). I have used this function many times previously, as well as on very similar spreadsheets to this one. When I try to use this now either the cell just shows the reference of the cell I am trying copy, or it does nothing at all. Both cells in this case are formatted to the same currency, it also occurs if you use numbers or text.

I am running the Mac version of MS 365 excel on a MacBook (running Ventura).

I have gone back to previous workbooks where it has worked in the past. If I try to add to an existing worksheet, this function now does not work!

As I normally link multiple worksheets within a workbook this has become a real issue.

Answer 2

I'm having the same problem with MS 365 with Mac v 14.2.1 (Sonoma). Which makes this latest xkcd comic ever more pertinent:

https://xkcd.com/2881/