Non-adjusting References in Formulas

[jlyh11]

I have a monthly report that requires me to get the sum of consectuve months. For e.g. For the month of Feb, I would need to get the sum of Jan + Feb. There would be a macro in the file that helps me to add a new column after column C. When the department column is added, it would cause my formula to go haywire and reference to the wrong column. Instead of $D7:$D49833, it would make a reference to $E7:$E49833. Also, instead of referencing to $G7:$G$49833, it willl make a reference to $H7:$H$49833.

I need the formulas to not adjust and stay as what it is given below when the macro adds a new column. How can this be done?


My formula is as such: =SUMPRODUCT(IFERROR(('Detail by cc'!$A$7:$A$49833="CFS(SG)")*('Detail by cc'!$C$7:$C$49833="Enhancement")*IFERROR('Detail by cc'!$D$7:$D$49833=A17,0)*OFFSET('Detail by cc'!$G$7:$G$49833,0,0,,MONTH('ITSR Dashboard'!$B$1)),0))*1000

[FDibbins]

Maybe change from a direct reference $D7:$D49833 change to INDIRECT(D7:D49833)

[MarvinP]

Hi,

It seems to me that using a Named Range in your formula(s) might be what you are asking for. If the number of rows expands then a Dynamic Named Range would be useful. Read about them at:

http://www.bettersolutions.com/excel...G820716330.htm

[jlyh11]

Hi Ford, I did as u advised but I got an error in my formula. How should I be changing this?
=SUMPRODUCT(IFERROR(('Detail by cc'!$A$7:$A$49833="CFS(SG)")*('Detail by cc'!$C$7:$C$49833="Enhancement")*IFERROR('Detail by cc'!Indirect(D7:D49833=A17,0)*OFFSET('Detail by cc'!G7:G49833,0,0,,MONTH('ITSR Dashboard'!$B$1)),0))*1000

[jlyh11]

Hi Marvin, Im still not sure how it could be done...

[bebo021999]

May be:

=SUMPRODUCT(IFERROR(('Detail by cc'!$A$7:$A$49833="CFS(SG)")*('Detail by cc'!$C$7:$C$49833="Enhancement")*IFERROR(INDIRECT("Detail by cc'!$D$7:$D$49833")=A17,0)*OFFSET('Detail by cc'!$G$7:$G$49833,0,0,,MONTH('ITSR Dashboard'!$B$1)),0))*1000

[jlyh11]

Hi Quang PT,

the value returns as "-" after trying to input what you suggested in the formula bar. It is unable to give me a value. Also, I would need the same to be done for the G7:G49833 part too.