Formulas appear but no values (Excel 97 SR2)

Topic

New Reply

Urgently need to resolve this:
two columns
each column contains formulas such as:
“=[details.xls]Sheet1!$A$34”
OR
=[details.xls]Sheet1!sys230
(where the sys230 is a named cell)
OR
=[details.xls]Sheet1!A34

(there’s a “square bracket”Details.xls”square bracket” immediately after the equals sign and before Sheet1! but when I post the square brackets and enclosed text disappears)
All works well until you edit a cell, (and it can be as simple as going to edit but not changing anything), then it all turns to rat. The cell does not show the resultant value but the complete formula, centred within the cell irrespective of cell justification.
Comparing the contents of two adjacent cells shows there is absolutely no difference in the cell contents, but the result is that one cell shows a value whereas the other shows the formula.
Things I have tried:
checked that Toolsoptionsviewformulas is not checked
checked that ToolsoptionsCalculation is set to automatic
checked that ToolsoptionsCalculation update remote references is checked
selected F9 to recalc
selected ctrl+alt+F9 to force calc

Any suggestions??
TIA
Alan
Cheshire
UK

Reply | Quote

Replies

Are these cells formatted as text? If so change them to general or a number format.

Reply | Quote

John, Thanks for suggestion. Cells are all formatted as numbers (and text where the result is a text string).
Alan

Reply | Quote

Hot off the press:
Just got a solution and what a gotcha!
MS actually has the solution but no fix to stop it happening in future, it’s one of those, yeah we know it happens but when it does just use this cure:
Open the worksheet that the error occurs in
Select EditReplace
Find what: =
Replace with: =
Select replace all
and is if by magic, the formula disappears from general view and the values appear as one would expect under normal circumstances.
I live and learn.
I believe MS recognises this as a ‘bug’ for MAC edition Excel 98 but no mention of Excel 97.
See MS Q184261
Thanks again
Alan

Reply | Quote

The formula was in the cell as text. That could happen be entering the formula while the cell was formatted as text. Changing the cell format to General or Number will not change the text to a formula without doing something like the find and replace that you did.

Reply | Quote