From: lharp21 on 24 Feb 2010 15:44 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)*(Expense!\$M\$2:\$M\$5874))) 5784 is the correct value and 5828 is the incorrect value. From: Max on 24 Feb 2010 18:40 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)*(Expense!\$M\$2:\$M\$5874))) > > 5784 is the correct value and 5828 is the incorrect value.  |