Using INDEX to prevent deleted columns from causing REF errors in forumal

[arleutwyler]

Hi all, I am trying use INDEX a range (J40:AR40) to prevent a macro that deletes columns in that range so it doesn't cause formula REF errors. The macro deletes a range of columns between J40:AE40. I have tried every possible idea I have on where to place the INDEX's but with no luck. Any help would be greatly appreciated.

=IF(C40="REMOVE",SUMIF($J$39:$AE$39,"Extended Price",J40:AE40)*-1,SUMIF($J$39:$AE$39,"Extended Price",J40:AE40))

Thanks
Alex

[dredwolf]

It's probably not the formula, but the Macro you need to correct...

[:) Sixthsense :)]

Try this...

=IF(C40="REMOVE",SUMIF(INDIRECT("$J$39:$AE$39"),"Extended Price",INDIRECT("J40:AE40"))*-1,SUMIF(INDIRECT("$J$39:$AE$39"),"Extended Price",INDIRECT("J40:AE40"))) 

[arleutwyler]

This formula works great but, I have multiple rows with this formula and I cant fill it down. Any suggestions?

Thank you,

[:) Sixthsense :)]

Just applying the suggested formula above and down won't affect the logic. Since the ranges were already surrounded by dollar sign so it denotes that the range wont get incremented / decremented.

Please correct me if my assumption is wrong

[arleutwyler]

I am using the dollar signs to set the Range ($J$39:$AE$39) for my criteria ("Extended Price") in my sumif statment. I need the sum range (J40:AM40) to be incremented to continue down the rows... if that makes any sense.

Thanks

[:) Sixthsense :)]

Hm.. Now I understand....

Try this....

=IF(C40="REMOVE",SUMIF(INDIRECT("$J$39:$AE$39"),"Extended Price",INDIRECT("J40:AE40"))*-1,SUMIF(INDIRECT("$J$39:$AE$39"),"Extended Price",INDIRECT("J"&ROW(J40)&":AE"&ROW(AE40))))

[arleutwyler]

This sort of works... The macro that deletes columns in that range, when run, deletes and shifts the range formula over and eventually causes a REF error.

If that makes sense.

[arleutwyler]

This sort of works... The macro that deletes columns in that range, when run, deletes and shifts the range formula over and eventually causes a REF error.

If that makes sense.

[:) Sixthsense :)]

Oops... Posted the formula without testing....

Just confirm me what is the start cell of the formula (i.e.) the cell on which the formula starts