Corrupt Cell?

Question

I have a spreadsheet that we have used for years and was built in Office 2003.

I went to make some updates but it seems one or two of the cells will not function correctly.  I have cleared contents and still have the same errors.

The formulas work fine in adjacent cells and if I copy the formula and place in another cell, it correctly calculates the answer.

The formula is =IF(CX3="yes",-'Convert to an Ular'!F2,'Convert to an Ular'!F2)

If I copy the this cell's above foruma to cz3 and then type, =cz3 in the bad cell, the formula in cz3 will calculate correctly but the second formula in the bad cell will show ####

If I just use ='Convert to an Ular'!F2, the apparently bad cell will do fine.   So it will pull data from another worksheet, but it will not calculate or pull data from the same spreadsheet.

I have been using Excel since the first release and have never seen anything like this.    Is it possible that one or two cells can be corrupt but the rest of the spreadsheet is fine?  If so, s there a repair?

Answer 1

Thanks.  The source was not text, it was number.

After more experimenting, I realized the cell failed whenever the number was negative.   

Your reply gave me an idea (that makes no logical sense).  I removed the number formatting for the bad cell and set it as "General."  The original format was simply "Number, Negative Number in Red, no parens."

The formula worked after I modified the format.  Another formula using the first formula worked also.

The kicker:  I then reformatted the same cell back to "Number, Negative Number in Red, no paren" and it worked fine.

The cell still has issues.

This fails:

=IF($CX$3="X",-'Convert to Ular First # 9'!H2,'Convert to Ular First # 9'!H2)

But this does not:

=IF($CX$3="YES",-'Convert to Ular First # 9'!H2,'Convert to Ular First # 9'!H2)

In adjacent cells, the "x" and "yes" versions work perfectly.  However, I can live with that problem, but I have no idea why this is happening.

Thank you for your response and inspiration.  Much appreciated.

Answer 2

Just a guess here but is the value in 'Convert to an Ular'!F2 text and not truely a number (Formatted as text). If that is the case then you can get some odd results. =- 'Convert to an Ular'!F2 will implicitly convert the text to a number if it can. Without the negative sign it will keep the value as text. Now if my column with is too narrow the number will be displayed as ### as the entire number can not be displayed. The text however will display correctly so long as it can overalp into the cell adjacent to the right.