Excel formula does not self adjust.

[dagda13]

Hi,

I have following cells:

Original Value	Adjusted Value
A	A
A
B	B

In column B, "Adjusted Value", I have the following formula in each cell to eliminate any duplicates in column A:

=IF(A2=A1,"",A2)
=IF(A3=A2,"",A3)
=IF(A4=A3,"",A4)

The problem occurs when I delete row 3 (the one with the blank value cell B3).

The formulas in column B do not adjust. Instead, I get the #REF! error:

Original Value	Adjusted Value
A	A
B	#REF!​

The formula in cell B3 is now:

=IF(A3=#REF!,"",A3)

When I delete row 3, shouldn't the formula automatically adjust to:

=IF(A3=A2,"",A3) ?

Why doesn't this happen? Is there anyway to set things up so the formulas will automatically adjust and not return a #REF! error?

My settings are on Automatic: Formulas ----> Calculation Options ----> Automatic.

Unfortunately, in my particular situation, I can't just remove the formulas and work with just the values. I need to able to delete rows with formulas that do not return #REF! errors.

Thanks in advance!

 

[jasonb75]

When I delete row 3, shouldn't the formula automatically adjust to:

When you delete row 3, the row is gone, row 4 moves up to take it's place. The remaining formulas below adjust, but any that refer to the row (or column) that has been deleted will return the #REF! error.

I think that you can work around it by using

=IF(A2=INDEX(A:A,ROW(A2)-1),"",A2)

 

[Fluff]

How about
=IF(INDEX(A:A,ROWS(A$1:A1))=A2,"",A2)

 

[jasonb75]

A$2:A2 would be better, Fluff. While unlikely, yours could still result in #REF! if row 1 is deleted.

 

[Fluff]

Excellent point Jason, I missed that.

 

[dagda13]

When you delete row 3, the row is gone, row 4 moves up to take it's place. The remaining formulas below adjust, but any that refer to the row (or column) that has been deleted will return the #REF! error.

I think that you can work around it by using

=IF(A2=INDEX(A:A,ROW(A2)-1),"",A2)

Thanks so much, Jason. This is exactly what I needed!

Best,
Nick