Turn off the formula correction feature

[JGreg7]

When you cut or delete cells, any formula that references those cells changes.

I am trying to compare columns of data using an "IF" function to show TRUE
or FALSE. In the process I wind up adding or deleting cells. Whenever I do
this, the folmula no longer works - it tries to reference the cell that is no
longer there.

What I would like to happen is that the formula references the new cell in
what was the previous location.

In example: if I delete A1, so that A2 becomes the new A1, I want the fomula
to look at the new A1 and ignore that I deleted a cell.

Is it possible to do this?

 

[Mike H]

Hi,

You need to use INDIRECT. If you struggle with setting up your formula using
this then post back.

example
=INDIRECT("A1")

will always reference A1 even if you delete the cell
--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''s razor (Abbrev)

 

[JGreg7]

Can I use it in a "IF" function?
=IF((INDIRECT(A1))=(INDIRECT(B1)),"yes"," ")
In this case the formula editor says it is "Volitile" and does not return a
value.

 

[JGreg7]

I tried that, however when you copy the formula, it copies identiaclly.
Instead of copying as
=INDIRECT("A1")
=INDIRECT("A2")
=INDIRECT("A3")

It copies as
=INDIRECT("A1")
=INDIRECT("A1")
=INDIRECT("A1")

When I use it as =INDIRECT(A1), it returns the value "#REF".

 

[JGreg7]

That works! Thank you very much for your help!