cell range not changing when refreshing linked data- sumproduct fo

[lharp21]

The cell range is not updating correctly to reflect the last row in the data
sheet that is being refreshed. 5878 is the correct number of rows and 5824
is not. I can find and replace in my formulas to correct the problem, but it
does it each time the data is refreshed. Any suggestions as to why?


=(SUMPRODUCT((Expense!$B$2:$B$5878=$C$2)*(Expense!$C$2:$C$5878=$C$3)*(Expense!$W$2:$W$5824=$A6)*(Expense!$M$2:$M$5878)))

 

[Max]

I gave you the INDIRECT option y'day, but received no feedback from you in
that thread. Looks like you're more interested in knowing why? One simple
hunch, because some people "refresh" data by actually deleting
cells/rows/cols, when they should be clearing cells/rows/cols with the DELETE
key. Deleting/cutting/moving actions will destroy/mess up any downstream
formulas pointing to the affected ranges.