Sumproduct across mutliple sheets - deleting row in one sheet

  • Thread starter Thread starter StephenT
  • Start date Start date
S

StephenT

So I spend 3 months building the most beautiful spreadsheets in the history
of humanity, then a user goes and breaks it. Here's the problem - it's about
deleting rows when you have a SUMPRODUCTs that span multiple sheets. on
those named ranges go all N/A

Let's say I have a SUMPRODUCT that spans different sheets

=SUMPRODUCT((Sheet1!C1:C10)*(Sheet2!C1:C10))


Now here's the rub : Let's say a user deletes row 5 in Sheet1. The
sumproduct then adjusts itself to

=SUMPRODUCT((Sheet1!C1:C9)*(Sheet2!C1:C10))

And proceeds to go the shape of a pear, giving out only a solitary splutter
of N/A.

Any suggestions for how I can avoid this? All I can think of is not allowing
the deletion of rows by protecting sheets...
 
Hi,

Try this.

=SUMPRODUCT((INDIRECT("Sheet1!C1:C10")*(INDIRECT("Sheet2!C1:C10"))))

And don't worry you won't have to wait long before they find another way of
destroying your worksheet!!
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Stop them deleting the rows (protect the sheet)!

Or use

=SUMPRODUCT((OFFSET(Sheet1!C1,0,0,10,1))*(OFFSET(Sheet2!C1,0,0,10,1)))
 
Back
Top