delete row, mess up formula

[kylefoley76]

i hate it when this happens, but sometimes when i delete rows, formulas in other cells that depend on that row will turn into the error message #value.

in the past it's been real easy to fix, because if there is a #n/a in that column then the formula will turn up #n/a, so i just get rid of the #n/a

but now there are no error signs in my column and the formula is turning up #value

here is my formula

=(SUMPRODUCT($I$2:$I$2997,--($G$2:$G$2997=T$1),--($D$2:$D$2997=$S3))+SUMPRODUCT($P$2:$P$3000,--($N$2:$N$3000=T$1),--($K$2:$K$2999=$S3)))*-1

 

[Jonmo1]

The best answer would probably be to not delete rows..

Try Clear Contents instead of delete.
Then sort the data to move the blanks to the bottom.


Hope that helps.

 

[kylefoley76]

yes, but everything is fine in the columns but my formula is still turning up errors

 

[JD_GFI]

Is it possible you deleted the row containing the explicit cell reference your formula points to?

 

[kylefoley76]

no, my formulas point to t1 s3 and then columns d g i k n p and all those columns are fine, plus t1 and s3 are also fine

 

[JD_GFI]

Your formula references a range in each column, not the column itself. Every time you delete a row you'll see the reference marking the end of the column range decrement. I'd guess that all of the columns started out ending at the same point (ie I3000, D3000, P3000, etc) but they don't now.

If you click inside the formula bar with the problem cell selected it will mark out the arrays it is looking at to calculate the formula. Make sure they highlight the area you expect them to.

 

[kylefoley76]

alright, i got it - all formula ranges have to be 2 to 3000, tyhanks