Prevent SUMPRODUCT from updating cells.

[McBOeric]

Hello,

I have this line of code that sort of works:

=SUMPRODUCT((DB!$J$4:DB!$J$161+DB!$K$4:DB!$K$161+DB!$L$4:DB!$L$161+DB!$M$4:DB!$M$161+DB!$N$4:DB!$N$161+DB!$O$4:DB!$O$161+DB!$P$4:DB!$P$161+DB!$Q$4:DB!$Q$161+DB!$R$4:DB!$R$161)*(DB!$H$4:DB!$H$161=DB!$AB$1:DB!$BW$1)*(DB!$B$4:DB!$B$161=A4))

The problem I am facing is that the workbook is constantly being updated with new lines getting inserted and 4 will change to a 5, then a 6, etc and make the value that is calculated wrong because it is not including from the 4th cell.

Is there any easy way to lock the formula so that it doesn't change when new lines are inserted?
I have looked at the INDIRECT function, but I can't seem to get that to work.

Here is a breakdown of what the formula does.

Rows J - R contain values that need to be added together if they match the day and they are not on the excluded list.
Row H contains sizes (eg 150x25) that need to be excluded if they match the values in AB1-BW1.
Row B contains the day that is matched with A4 being 3rd, A5 being the 4th etc.

Any information that you could provide to point me in the right direction would be greatly appreciated.

Regards,
Luke.

 

[yky]

See if this works:

=SUMPRODUCT((INDIRECT("db!$J$4:$J$161")+INDIRECT("db!$K$4:$K$161")+INDIRECT("db!$L$4:$L$161")+INDIRECT("db!$M$4:$M$161")+INDIRECT("db!$N$4:$N$161")+INDIRECT("db!$O$4:$O$161")+INDIRECT("db!$P$4:$P$161")+INDIRECT("db!$Q$4:$Q$161")+INDIRECT("db!$R$4:$R$161"))*(INDIRECT("db!$H$4:$H$161")=INDIRECT("db!$AB$1:$BW$1")*(INDIRECT("db!$B$4:$B$161")=INDIRECT("A4"))))

 

[McBOeric]

=SUMPRODUCT((INDIRECT("db!$J$4:$J$161")+INDIRECT("db!$K$4:$K$161")+INDIRECT("db!$L$4:$L$161")+INDIRECT("db!$M$4:$M$161")+INDIRECT("db!$N$4:$N$161")+INDIRECT("db!$O$4:$O$161")+INDIRECT("db!$P$4:$P$161")+INDIRECT("db!$Q$4:$Q$161")+INDIRECT("db!$R$4:$R$161"))*(INDIRECT("db!$H$4:$H$161")=INDIRECT("db!$AB$1:$BW$1")*(INDIRECT("db!$B$4:$B$161")=INDIRECT("A4"))))

[/QUOTE]


The first part of the formula works perfectly, but the second part will cause an #VALUE! or #N/A error.
This second part is causing the issue:

*(INDIRECT("db!$H$4:$H$161")=INDIRECT("db!$AB$1:$BW$1")*(INDIRECT("db!$B$4:$B$161")=INDIRECT("A4")))

Only the 4-161 cells need to be indirect, the AB1-BW1 and A4 cells do not change or move.

I have tried to tweak the formatting with no luck.


Thanks,
Luke.

 

[yky]

I did not get that error. On mine, it gave 0 because I only had blank sheets.

Only the 4-161 cells need to be indirect, the AB1-BW1 and A4 cells do not change or move.

Not sure what you mean. Indirect is used so AB1:BW1 and A4 do not change.

 

[McBOeric]

I finally managed to figure the issue out.

It turns out that a single missing ) was causing all the problems here:

(INDIRECT("db!$H$4:$H$161")=INDIRECT("db!$AB$1:$BW$1")

I just had to add another ) to the end to get it all working properly.

Many thanks for the solution, it is working perfectly.