Strange issue here - sorry, it’s a long explanation… . I have a workbook of several sheets. The first sheet is a set-up sheet [called “set-up”] which has basic default values. Other sheets take values from this sheet. One such value on this sheet is a text string representing a time period - eg " 2023 - 2024 ". The next sheet takes this string and concatenates it with the word “Accounts” to give the title “Accounts 2023 - 2024”. This has worked perfectly for several years - until now. I changed the position of the text string on the set-up sheet [from cell H11 to cell H5] then went to the other sheet to change the formula which was:
="Accounts "&$'set-up'.H11 - which was working correctly…
to
="Accounts "&$'set-up'.H5 - which does not now work !
Now, the cell will only show this formula and it will not show the value, which should be "Accounts 2023 - 2024 "
As I say, this has worked perfectly well for years, and I can’t fathom why it should suddenly not work now.
I do not think it has anything to do with the show formula/show value toggle, as I have tried both, but neither makes any difference to this cell.
Oddly, I cannot reproduce the error on a new empty sheet
Please look at your formula in the formula bar. Perhaps you will see such an apostrophe before the equals sign?
And what will you see on the Numbers tab in the Categories field if you press CTRL + 1? Perhaps this particular cell is formatted as Text?
1 Like
- Look at Tools>Options>Calc>View>“Show Formulas”. It should be off unless you are going to debug your formulas.
- Formula cells should not be formatted as text.
2.1. Remove number format “Text” from the cells in question.
2.2. Since formatting attributes must not change any values, you will see an apostrophe in front of the formula marking the content as literal string (only visible in the formula bar). Remove that.
I did spot the apostrophe and wondered why it was there. I deleted it, and it all seems to work OK again now.
Thanks.
1 Like
Next time, just Cut and Paste; this must update the reference in the other sheets automatically.