Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

EXCEL shows formula itself instead its result.

Posted on 2013-06-13
12
Medium Priority
?
500 Views
Last Modified: 2013-07-11
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.
0
Comment
Question by:MaplesITHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39243771
Is there a space or single quote before the equal sign?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39243773
When you change a cell's format from Text to General you have to re-enter the formula for it to take.

Kevin
0
 
LVL 50
ID: 39243797
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:MaplesITHK
ID: 39243819
Hi Zorvek,

Try yours one but no luck.

Hi Teylyn,

Will try yours one.

Thanks all!
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39243868
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
0
 
LVL 50
ID: 39243931
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.

screenshot
0
 

Author Comment

by:MaplesITHK
ID: 39243953
Checked and such options wasn't been enabled by default. :(
0
 
LVL 50
ID: 39243962
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?
0
 

Author Comment

by:MaplesITHK
ID: 39243986
will try bcos it contains some p&c info
0
 
LVL 10

Accepted Solution

by:
Gašper Kamenšek earned 720 total points
ID: 39244178
If I had to guess I would say it's

pic
Formulas/Show Formulas

Somewhere, someone pressed Ctrl + m by mistake...

Hope it helps...
0
 
LVL 50
ID: 39244444
@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.
0
 
LVL 50
ID: 39317276
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question