Reference Error Workaround

[Lisa928]

I want to be able to delete rows (green, blue, purple; but these will always change) and have the simple calculation (previous row minus one) recalculate. When I delete them I get a reference error. Is there another calculation that I could us?

(I tried downloading the add-in to put in an Excel image, but Colo’s site is not working)

red 1
orange 2
yellow 3
green 4
blue 5
purple 6
black 7
white 8
brown 9


what I want
red 1
orange 2
yellow 3
green (deleted)
blue (deleted)
purple (deleted)
black 4 (were REF error)
white 5 (were REF error)
brown 6 (were REF error)

 

[Datsmart]

You will need to re-write the formulae when the reference row is deleted.
Are you doing the row deletion via code or manually?
If by code, you can include the formula re-write there.

 

[Lisa928]

delete them manually as they always change. I am okay writing good to redo formula, but just wondering if there was a different formula. This spreadsheet will be given to someone who is not that good at Excel so I was hoping not to have to include a macro.