Deleting row creating a ref issue in excel

Automation Monkey 0
Viewed 38

I have a helper column with the following code

=IF(A1<>A2,1-SUM(B1),SUM(B1))

The table looks like

Table

When I delete a row, say row 21 in this example, I end up with a ref issue in cell B21 -

=IF(#REF!<>A21,1-SUM(#REF!),SUM(#REF!))

Is there any way to resolve this, perhaps with a way to sequence the formula in cell B2 such that it automatically runs the calculation and corrects based on rows being removed?

excel-formula excel
2 Answers

In B2 use: =IF(INDEX(A:A,ROW()-1)<>INDEX(A:A,ROW()),1-SUM(INDEX(B:B,ROW()-1)),SUM(INDEX(B:B,ROW()-1)))

By using INDEX you can avoid referencing cells that may be deleted, which is causing the issue you have.

P.b0

You can use the offset function to avoid this and always make the formula look to the above cell like this:

=IF(OFFSET(A2,-1,0)<>A2,1-SUM(OFFSET(B2,-1,0)),SUM(OFFSET(B2,-1,0)))

This tells to use A2 but 1 row less, making it A1. (And same for B2.)

tnavidi0
Related