MaplesITHK
asked on
EXCEL shows formula itself instead its result.
In Excel 2010
when user tries to insert the cell reference to particular cell
e.g. in cell A10 of sheet1 type =sheet2!A3
it usually returns 0 (cell A3's format is text from Sheet2)
but it shows the definition of the formula "=sheet2!A3" instead of its value. I've change both cells to general but still got the same error.
Any ideas?
Cheers.
when user tries to insert the cell reference to particular cell
e.g. in cell A10 of sheet1 type =sheet2!A3
it usually returns 0 (cell A3's format is text from Sheet2)
but it shows the definition of the formula "=sheet2!A3" instead of its value. I've change both cells to general but still got the same error.
Any ideas?
Cheers.
When you change a cell's format from Text to General you have to re-enter the formula for it to take.
Kevin
Kevin
To quickly re-evaluate all cells in the worksheet without having to manually edit each cell, you can do a find/replace. Open the Replace dialog with Ctrl-H, then in the Find What box enter a = sign. In the Replace With box, also enter a = sign. Hit "Replace All" and all formulas in the sheet will be recalculated (unless they are still in Text format)
cheers, teylyn
cheers, teylyn
ASKER
Hi Zorvek,
Try yours one but no luck.
Hi Teylyn,
Will try yours one.
Thanks all!
Try yours one but no luck.
Hi Teylyn,
Will try yours one.
Thanks all!
Perhaps you have set the worksheet to display formulas instead of values. Try pressing CTRL+`
If that doesn't work, press CTRL+` again to toggle back to show values.
Kevin
If that doesn't work, press CTRL+` again to toggle back to show values.
Kevin
And the long way for Kevin's keyboard shortcut tip is File > Options > Advanced > Show Formulas instead of calculated values. You want to make sure it is NOT selected.
ASKER
Checked and such options wasn't been enabled by default. :(
Let's recap:
The cell has the formula
=sheet2!A3
The formula does not have any leading spaces before the = sign.
The cell is formatted with General, not Text,
The worksheet is set to show values, not formulas
You have updated the formula by editing it, or by doing a replace = with = operation.
It still shows the formula? That is most unusual.
Can you post a sample file with that behaviour?
The cell has the formula
=sheet2!A3
The formula does not have any leading spaces before the = sign.
The cell is formatted with General, not Text,
The worksheet is set to show values, not formulas
You have updated the formula by editing it, or by doing a replace = with = operation.
It still shows the formula? That is most unusual.
Can you post a sample file with that behaviour?
ASKER
will try bcos it contains some p&c info
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@GasperK, cool! Now we have keyboard shortcut, Excel Options and ribbon command all in one thread. All these do the same thing = change the same toggle switch. Great for people to have options.
Hang on, why did you accept that answer? Zorvek suggested to check the formulas/values toggle with Ctrl-~ and I showed you a way to check it with the Excel options.
You said that this setting was NOT the cause.
How come, then, that the ribbon way of toggling that setting is the answer if the setting was not applied???
Clicking the ribbon button is just another way to change that setting. Zorvek and I gave you two other ways.
Seems like you did not really follow through with the first two suggestions to check that setting. If anything, this should go to Zorvek, because he was the first to point to the values/formulas setting. But if you award points to any answer later than Zorvek's, it should be a split.
You said that this setting was NOT the cause.
How come, then, that the ribbon way of toggling that setting is the answer if the setting was not applied???
Clicking the ribbon button is just another way to change that setting. Zorvek and I gave you two other ways.
Seems like you did not really follow through with the first two suggestions to check that setting. If anything, this should go to Zorvek, because he was the first to point to the values/formulas setting. But if you award points to any answer later than Zorvek's, it should be a split.
Kevin