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