How do you delete rows without affecting formulas?

**modytrane** · 09-28-2009, 01:15 PM

In general, when you write a formula:

In Cell A1:

=Sum($A$4:$A$100)

And then a macro deletes 10 Rows from that range, the formula changes to:

=Sum($A$4:$A$90)

Is there a way to fix that range even when rows are deleted?

I can think of a few workarounds [including some VBA], but I would think Excel should have an easier way already built-in.

Thanks for any help or assistance.

modytrane

**DonkeyOte** · 09-28-2009, 01:19 PM

INDEX

=SUM(INDEX(A:A,4):INDEX(A:A,100))

You can use INDIRECT too but unlike INDEX that is Volatile.

If you intend to delete Column A in entirety then you need to adjust the INDEX range accordingly (to a matrix and add 1 (column_num)) which will work unless you intend to delete the sheet in full... at which point you need INDIRECT.

**NBVC** · 09-28-2009, 01:19 PM

Try using instead

=Sum($A$4:INDEX($A:$A,100))

**daniel.preda** · 09-28-2009, 01:22 PM

one way to do it... what i use... is to utilise the INDIRECT function... I mean, instead of
=SUM(D2:D100)
use
=SUM(INDIRECT("D2:D100"))

But this is more difficult to copy, because it just takes range like a simple text... and cannot modify it...
What I do, I use the normal function, and after, with a series of replaceses i transform the formula with INDIRECT...

But this is just one way...

**modytrane** · 09-28-2009, 02:46 PM

Thank you all for you responses.
Here's the formula I am using. How do you modify this formula?
I tried the index method, it doesn't work in this formula.

=IF(ISBLANK(C6),"",SUMIF('Points List'!$C$4:$C$2000,C6,'Points List'!$B$4:$B$2000))

The Formula is on one sheet and it references a range on another sheet [i.e. "Points List"]. I have macros that delete rows from that range.

Any suggestions?

Thank again,
modytrane

**NBVC** · 09-28-2009, 02:49 PM

You tried?

=IF(ISBLANK(C6),"",SUMIF('Points List'!$C$4:INDEX('Points List'!$C:$C,2000),C6,'Points List'!$B$4:INDEX('Points List'!$B:$B,2000)))

**DonkeyOte** · 09-28-2009, 02:51 PM

Though I would make the point that using C4:INDEX(...) rather than INDEX:INDEX is still open to error if row 4 is deleted.

**modytrane** · 09-28-2009, 02:57 PM

Thanks NBVC, it works.
I wasn't using proper syntax.

DonkeyOte,
Your point is well taken.
However, in my case the macro stops at Row5. I had the extra row in my formula just for safety.

Thanks to both of you, learned something new.
modytrane.