The range in the Excel formula changes after insert-shift, how can I stop it changing?

2
votes

I have an Excel 2010 formula in an Excel cell like this (the formula lies in column T):

=SUMIF($B$14:$B$44335;$T8;$E$14:$E$44335)

This formula does this: It finds the cells in the Range("B14:B44335") which have the value as Range("T8") (For example, between Range("B192:B370")), then it sums up the values in the column E (For example, sums up between Range("E192:E370")).

The formula works fine, but a background VBA-code couses problem. This VBA code sometimes extends or shorten Range("B14:E44335"). The VBA-code uses insert shift:=xlDown or delete shift:=xlUp for extending or shortening.

Insert shifting (inserting 178 rows) in VBA causes that the formula changes like this:

=SUMIF($B$192:$B$43623;$T8;$E$192:$E$43623)

So the formula also shifts from B14 to B192.

This is strange, because the formula is in the column T, and the VBA-code has nothing to do with the column T.

I tried to give a name to the range like, when I write Countries, then Excel selects the Range("B14:E44335"), so

=SUMIF(Countries;$T8;$E$14:$E$44335)

But i got the same problem again. After Insert Shift, Countries shows Range(B192:B43623")

In anyway, how can I keep the range in my formula permanent, no matter what happens in other cells?

1
vbaexcelexcel-formula
re: $B$14:$B$44335 are 14 and 44335 "fixed" and never change? or are they actually based on some logic ? - Ditto
Excel's functionality will automatically update row and column references when rows or columns are inserted / deleted across a formula range reference that intersects the inserted / deleted cells. For example, if the rows were inserted / deleted after row 14, the B14 in your example would not be affected, only the bottom rows. Since you say the code does not even affect column T, can you change the code to only insert / delete the necessary rows / columns instead of the EntireRow or EntireColumn? In this way, your existing formula references would not be affected. - Scott Holtzman
The other option (a real hack, but ...) is to have the VBA code reset your formula after the insertion / deletion. - Scott Holtzman
@ditto it must be a fixed range, always the same range - user3714330
@scott-holtzman VBA-code insert-shift into the beginning of the range also sometimes. So that's why it changes the B14 when VBA does insert shift to the beginning of the range - user3714330

1 Answers

2
votes

try this:

  =SUMIF(offset($b$1,13,0,44321, 1), $t8, offset($e$1,13,0,44321))

This uses OFFSET to create the fixed range: 14 to 44335 .. if that should be a fixed range, you should be golden. If not, you need to figure out some formula/logic to calculate the 13 and the 44321 .. ;)

For reasons mentioned by Scott in comments above, Excel is adjusting your ranges, offset bypasses that ;)