We help IT Professionals succeed at work.

Correct Formula: but doesn't work

mikecox_
mikecox_ asked
on
Medium Priority
333 Views
Last Modified: 2012-05-12
I can't understand why this formula can be working in all the cells except the ones I re-entered them in.  Even when I copy the formula from the cells where it is working and paste it, it doesn't work.

I've checked the cell formatting and it seems to be in correct; like the "number" tab is set to Text.

"Milan" is the A6 cell
cell-not-copied.jpg
Comment
Watch Question

Commented:
What exact you are facing..?

can you please post the sample excel file ?
Check the thread below:

http://chandoo.org/wp/2010/04/12/excel-formulas-are-not-working/

It might helps.

Ed
Is the current sheet name is "Start 2009" and are you trying to reference the same cell where you are entering the formula ? Then it will not work.

It would be better if you attach a sample file.



CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
The Number format should not be set to Text. Set it to anything else and then re-enter the formula.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

check if you have calculation mode set to manual. If so, change it to automatic. Excel Options > Formulas > Calculation > Automatic

cheers, teylyn
Kannan KManager - Engineering
CERTIFIED EXPERT

Commented:
Hi,

Check the formula which is not working, before the '=' sign is there any single quote. if it is there please remove it and press enter, it will work. when you say single quote before starting the formula, excel will treat as a string.

KK,

Author

Commented:
Ok, here is the file. The formulas in A3, 4 and 5 are exactly like the ones in A6 on, yet the formulas in A3, 4 and 5 display as formulas.  There is no error message related to the formula, so why does the formula display instead of the text that is referenced by the formula? The exact same formula is working in A6-A45

I am referencing a different page; trying to copy the text in column A, on the page "Start 2009" into column A on page "2009 and 2010"

I have the feeling that I'm going to be embarrassed by the answer.  I so rarely work with XL that I find that I forget the simplest things.  So be kind (-:
Budget-tax-report-2009-10-and--1.xlsx

Author

Commented:
He's an observation I made while checking the ref Ed posted.  I hit Show Formulas and noticed the ones in question are indented; but there is no space in front of the "=" symbol in the fields in question
Difference.jpg
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
As I said earlier:
"The Number format should not be set to Text. Set it to anything else and then re-enter the formula."

Author

Commented:
Ok, it appears that I have discovered the problem.  In my question I noted that I had the cell formatted for text, as apposed to Number.  In the link Ed posted it said the cell should be formatted to General.

When I changed the formatting in the cells showing the formula the cell displayed the text; problem solved.

However, look at the attached file.  The cell A9  is formatted "Text", and the formula isn't displaying, the text is displaying.  How can that be?
General.jpg

Author

Commented:
Very good reference, explained exactly why I was having this problem

Explore More ContentExplore courses, solutions, and other research materials related to this topic.