Cell reference returning incorrect value

[JohnOC]

Hello,
I have a cell (cell A) on one tab referencing a cell on another tab (cell
B), but returning an incorrect value. Is there a way to fix this on a
wholescale level. It was not a calculation problem (tried F9). I was able
to fix the problem by putting the cursor at the end of the formula in cell A
and hitting enter, but this could be happening throughout the spreadsheet.
Any advice or help is greatly appreciated.

Thank you,

John

 

[Gord Dibben]

A and B are not cells................they are columns.

Cells are like A1 or B1

What is the incorrect value you get and what do you think it should be?

Could you post your formula?

Sounds like maybe you have numbers that are formatted as text.

You could try to select all cells and re-format to General.

Now select an empty cell and copy it.

Select the cells with numbers................not formulas............and
paste special>add>ok>esc

Do formula now calculate?

Another trick is to to select all and Edit>Replace

What: = sign

With: = sign

Replace all.


Gord Dibben MS Excel MVP

 

[JohnOC]

Hello Gord,

Thank you very much for replying. It is appreciated. The details of my
issue are as follows:

I have two tabs in a file: "Q1 09 For" and "YE For." Cell I36 on "YE For"
contains the formula =+'Q1 09 For'!S36, which returns a value of 70,800.
However, cell S36 on tab "Q1 09 For" contains the value 65,800. After
putting the cursor at the end of the formula in cell I36 on "YE For" and
hitting enter, the value changed to the correct 65,800. My problem is that I
don't know how many times this might be happening in the file and was hoping
there was a way to correct everything at once. Again, it was not a
calculation problem (F9 did not help).

Thank you again,

John