Editing a formula

[Dawn]

I am editing a formula in my worksheet which returns the correct result. As
soon as I make a change to the formula the cell shows the result as
"#######....", but the formula tollbar shows the correct result which is two
initials, "DH". I cannot fix the cell to show the correct result of "DH"
instead of the "##########...".

Has anyone else had this problem? Autocalculate is on, the formating for
the cell is correct. I am at a loss.

 

[Bob Phillips]

What is the formula? It doesn't sound as though the column is too narrow.
Check the format in Format>Cells as well, it should be General.

 

[Dawn]

I have tried that and the result still shows as "#####...."

It's the starngest thing.

 

[Sheeloo]

In the formula bar you should see the FORMULA not the result DH?

Also is it an array formula?

Try to press CTRL-ENTER-SHIFT instead of just ENTER...

It would have been great if you had pasted the formula... Pl. do so now.

 

[Dave Peterson]

If a cell is formatted as text and is between 255 and 1024 characters long, then
you'll see those #'s.

Try changing the numberformat of the cell to General (or anything but text) and
then hit F2 followed by enter to reevaluate the formula.

 

[Dawn]

Here is the formula

=IF(OR(F2="Atlanta",F2="Baltimore/Wash. D.C.",F2="New
Jersey",F2="Charlotte",F2="Philadelphia",F2="Houston",F2="Orlando",F2="South
Jersey",F2="Northern New Jersey",F2="Central
Pennsylvania",F2="Miami",F2="Boston",F2="Southern New
Jersey",F2="Minnesota"),"DH",IF(OR(F2="Dallas",F2="San
Antonio",F2="Cincinnati",F2="Columbus",F2="Indiana",F2="Kansas
City",F2="Louisville",F2="Memphis",F2="Nashville",F2="Minneapolis",F2="Indianapolis",F2="SanAntonio",F2="San
Luis Potosi, MX",F2="Guadalajara, MX",F2="Tijuana, MX",F2="Monterrey,
MX"),"JW",IF(OR(F2="Chicago",F2="Denver",F2="Phoenix",F2="San
Francisco",F2="Seattle",F2="Los Angeles",F2="Northern California",F2="Salt
Lake City",F2="Southern California"),"MP","")))

Essentially, I have three initials that can be returned based on the formula
"DH", "JW", or "MP". The formula is working correctly, and the cell has
previously showed the correct result, but as soon as I change, as an example,
the first "F2="Atlanta" from showing "DH" as the result to "JW", when I click
on the fx to see the results of the formula it shows the correct "JW" result
but the cell in which the formula is embedded shows "######".

here is an example of that.

Asset Manager Name DCT Regional Leasing Representative (code) Cape
MB JT ###############################################################################################################################################################################################################################################################
MB JT JW

So it went from showing JW to the string of ########

 

[Dawn]

That fixed it! Thanks!!!!

If a cell is formatted as text and is between 255 and 1024 characters long, then
you'll see those #'s.

Try changing the numberformat of the cell to General (or anything but text) and
then hit F2 followed by enter to reevaluate the formula.

 

[Sheeloo]

How are you making the change?

I tried it and I did not get any problem.

Also I think you should change your setup.

Enter the states in Col A in any sheet (say Sheet2) and enter the
corresponding initial in Col B (you can select multiple cells, type DH and
press CTRL-Enter to save effort)

then use this formula in your main sheet
=VLOOKUP(F2,Sheet2!A:B,2,False)

or

=IF(ISNA(VLOOKUP(F2,Sheet2!A:B,2,False)),"",VLOOKUP(F2,Sheet2!A:B,2,False))

to supress #NA errors...

 

[Sheeloo]

I think that was the first suggestion (from BOB) you had got

"What is the formula? It doesn't sound as though the column is too narrow.
Check the format in Format>Cells as well, it should be General. "

 

[Dawn]

I had tried changing the format to "general" but that alone didn't work. The
hit F2 then enter did the trick. I am not sure what the F2 button did, but
the combination of changing the format to "general", hit F2, the enter to
recalculate did the trick.

 

[Sheeloo]

Ok

F2 puts you in Edit mode... it forced recalculation of the formula...

Suggest you try the VLOOKUP solution... it is easier and will save you lot
of effort in future...
You should learn to use it even if you don't use it for this particular sheet.

 

[Dave Peterson]

Hitting F2 puts you in Edit mode. Then enter says you're done making the
change.