Excel formula does not show values if formula edited

Topic

New Reply

This was a tough one to find a title for but …….

Anyone encountered this on Excel 2007 running SP2

If a formula is copied and pasted into another cell then it will calculate and show the answer
BUT…
If you type a formula into a cell OR Paste a formula into the formula bar, then it just shows as
as formula with the = at the front.

I have heard that this is a bug, and that it can be fixed by formatting the cell as Text.
BUT.. This solution does not work, it still says as a formula.

AND … In case anyone asks, it is not set to show formulas, it only happens on some cells.
If you copy a working formula from another cell, and paste it into same cell, it is OK.
If you take the same formula and paste it into the formula bar, it shows as a formula.

Is there something to stop this happening, or is it just a BUG (surely not) in Excel.

Reply | Quote

Replies

Andrew,

Could you please post an example workbook where this is happening? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Probably not.

It is not my data, and is highly confidential anyway.
AND.. I am not sure I will be allowed a copy to post even if sanitised.
If I can I will upload it.
I am also going to see if it still ocurs in 2010, but I cannot do that until Thu.

Reply | Quote

Formatting the cell as Text causes that problem, rather than curing it.

Reply | Quote

Formatting the cell as Text causes that problem, rather than curing it.

Thanks Rory.

Interesting, I was just going on hearsay on that one.
That is what they told me, I had not encountered the issue.

I do know that the data for the main bulk is imported,
but the columns that are showing the issue are ones that have been inserted after the import.
I just wonder if they got the wrong end of the stick regarding text.

Infact, thinking ??????????????????????? about it,
I reckon the cells may have been set as text format after the import.
Then new columns inserted are getting the same text format as adjacent ones.
If you change the format back to General it does not change it back to an answer unless you edit the formula.

Hmmmmm.

I will let you know the outcome shortly ……..
And I have just tested that theory and VOILA!

I probably should have looked at the sheet in more detail, but they wouldn’t let me play with it!
AND I was a tad preoccupied at the time with some complex vba coding.

29295-Doh3

Many Thanks for a nudge in the right direction!

Reply | Quote