I have a workbook with multiple sheets. Some sheets contain imported data
and some contain sumproduct formulas referencing the data. When the data is
refreshed, the formulas all return #N/A values. The range in the formula is
not being updated to reflect the new rows that have been added when the data
sheets are refreshed. This does not happen consistently, but it is
frustrating. It is easy enough to find and replace the incorrect numbers,
but it repeats the same pattern again when the data is refreshed.

=(SUMPRODUCT((Expense!$B$2:$B$5874=$C$2)*(Expense! $C$2:$C$5874=$C$3)*(Expense!$W$2:$W$5828=$A6)*(Exp ense!$M$2:$M$5874)))

5784 is the correct value and 5828 is the incorrect value.

Think you can use INDIRECT to always point to fixed ranges
Eg: =SUMPRODUCT((INDIRECT("Expense!B2:B5874")=$C$2)*.. .)
Wrap the INDIRECT for all the other ranges involved in your expression. Note
that within Indirect you can drop the $ signs since its a text string. voila?
hit YES below
--
Max
Singapore
---
"lharp21" wrote:
I have a workbook with multiple sheets. Some sheets contain imported data
and some contain sumproduct formulas referencing the data. When the data is
refreshed, the formulas all return #N/A values. The range in the formula is
not being updated to reflect the new rows that have been added when the data
sheets are refreshed. This does not happen consistently, but it is
frustrating. It is easy enough to find and replace the incorrect numbers,
but it repeats the same pattern again when the data is refreshed.

=(SUMPRODUCT((Expense!$B$2:$B$5874=$C$2)*(Expense! $C$2:$C$5874=$C$3)*(Expense!$W$2:$W$5828=$A6)*(Exp ense!$M$2:$M$5874)))

5784 is the correct value and 5828 is the incorrect value.