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.

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

--
__________________________________
HTH

Bob

"Dawn" wrote in message
...
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.

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

It's the starngest thing.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Dawn" wrote in message
...
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.

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.

"Dawn" wrote:

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.

Here is the formula

=IF(OR(F2="Atlanta",F2="Baltimore/Wash. D.C.",F2="New
Jersey",F2="Charlotte",F2="Philadelphia",F2="Houst on",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="Phoen ix",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) Capex
MB JT ################################################## ################################################## ################################################## ################################################## ################################################## #####
MB JT JW

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


"Sheeloo" wrote:

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.

"Dawn" wrote:

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.

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)),"",VLOOKU P(F2,Sheet2!A:B,2,False))

to supress #NA errors...


"Dawn" wrote:

Here is the formula

=IF(OR(F2="Atlanta",F2="Baltimore/Wash. D.C.",F2="New
Jersey",F2="Charlotte",F2="Philadelphia",F2="Houst on",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="Phoen ix",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) Capex
MB JT ################################################## ################################################## ################################################## ################################################## ################################################## #####
MB JT JW

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


"Sheeloo" wrote:

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.

"Dawn" wrote:

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.

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 wrote:

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.

--

Dave Peterson

That fixed it! Thanks!!!!

"Dave Peterson" wrote:

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 wrote:

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.

--

Dave Peterson

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 FormatCells as well, it should be General. "

"Dawn" wrote:

That fixed it! Thanks!!!!

"Dave Peterson" wrote:

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 wrote:

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.

--

Dave Peterson

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" wrote:

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 FormatCells as well, it should be General. "

"Dawn" wrote:

That fixed it! Thanks!!!!

"Dave Peterson" wrote:

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 wrote:

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.

--

Dave Peterson

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.

"Dawn" wrote:

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" wrote:

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 FormatCells as well, it should be General. "

"Dawn" wrote:

That fixed it! Thanks!!!!

"Dave Peterson" wrote:

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 wrote:

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.

--

Dave Peterson

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

Dawn wrote:

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" wrote:

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 FormatCells as well, it should be General. "

"Dawn" wrote:

That fixed it! Thanks!!!!

"Dave Peterson" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson