mikecox_

asked on

# Correct Formula: but doesn't work

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

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

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
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.

It would be better if you attach a sample file.

The Number format should

**not**be set to Text. Set it to anything else and then re-enter the formula.
Hello,

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

cheers, teylyn

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

cheers, teylyn

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,

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,

ASKER

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

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

ASKER

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

Difference.jpg

As I said earlier:

"The Number format should

"The Number format should

**not**be set to Text. Set it to anything else and then re-enter the formula."ASKER

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

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

ASKER

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

can you please post the sample excel file ?