Sumproduct across mutliple sheets - deleting row in one sheet

[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...

 

[Mike H]

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.

 

[Bob Phillips]

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)))