Prev: Multiple lists in a cell
Next: Finding the cell loacation that matches a specific value in a rang
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.
 | 
Pages: 1
Prev: Multiple lists in a cell
Next: Finding the cell loacation that matches a specific value in a rang