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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top