Adding a new row is messing up my SUMPRODUCT
 

I am using SUMPRODUCT to calculate across 30 different spreadsheets. I
finally have it working so that it will go to a spreadsheet and lookup an
identifier and return a value assocated with that identifier. The problem is
if I add a new row to one of the sheets, it messes up my summary page. I
have even ensured the new row has the correct data and no blanks. What can I
do to protect the SUMPRODUCT even if I add a new row to a spreadsheet

Here's a sample of my formula
Formula lives on Summary and I add a new row to '1251 Beta' sheet

=SUMPRODUCT(('1251 Beta'!$B$10:$B$38='RESOURCE ALLOCATION'!$B61)*('1251
Beta'!C$10:C$38))

Adding a new row is messing up my SUMPRODUCT
 

Maybe

=SUMPRODUCT(SUMIF(INDIRECT("'1251 Beta'!$B$10:$B$38"),'RESOURCE
ALLOCATION'!$B61)*(INDIRECT("'1251 Beta'!C$10:C$38")))


--
__________________________________
HTH

Bob

"Jennifer" wrote in message
...
I am using SUMPRODUCT to calculate across 30 different spreadsheets. I
finally have it working so that it will go to a spreadsheet and lookup an
identifier and return a value assocated with that identifier. The problem
is
if I add a new row to one of the sheets, it messes up my summary page. I
have even ensured the new row has the correct data and no blanks. What
can I
do to protect the SUMPRODUCT even if I add a new row to a spreadsheet

Here's a sample of my formula
Formula lives on Summary and I add a new row to '1251 Beta' sheet

=SUMPRODUCT(('1251 Beta'!$B$10:$B$38='RESOURCE ALLOCATION'!$B61)*('1251
Beta'!C$10:C$38))

Adding a new row is messing up my SUMPRODUCT
 

One obs. Based on your sample formula, any insertions of new rows uptill row
38 (the last row) would be auto-adjusted by Excel. You could extend the end
row to a "safe" point beyond which it is unlikely that new rows would be
inserted. Since sumproduct is calc-intensive, balance your estimation of that
safe point to be the smallest range which is large enough to cover. Any good?
hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Jennifer" wrote:
I am using SUMPRODUCT to calculate across 30 different spreadsheets. I
finally have it working so that it will go to a spreadsheet and lookup an
identifier and return a value assocated with that identifier. The problem is
if I add a new row to one of the sheets, it messes up my summary page. I
have even ensured the new row has the correct data and no blanks. What can I
do to protect the SUMPRODUCT even if I add a new row to a spreadsheet

Here's a sample of my formula
Formula lives on Summary and I add a new row to '1251 Beta' sheet

=SUMPRODUCT(('1251 Beta'!$B$10:$B$38='RESOURCE ALLOCATION'!$B61)*('1251
Beta'!C$10:C$38))

Adding a new row is messing up my SUMPRODUCT
 

Max, thanks for the suggestion. I did move my last row to 99 but when I
inserted, it still gave my summary page NA's. My quick fix was when I
inserted a row, I'd go to the end (row 99) and delete a blank row. That in
return, fixed the summary page.

"Max" wrote:

One obs. Based on your sample formula, any insertions of new rows uptill row
38 (the last row) would be auto-adjusted by Excel. You could extend the end
row to a "safe" point beyond which it is unlikely that new rows would be
inserted. Since sumproduct is calc-intensive, balance your estimation of that
safe point to be the smallest range which is large enough to cover. Any good?
hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Jennifer" wrote:
I am using SUMPRODUCT to calculate across 30 different spreadsheets. I
finally have it working so that it will go to a spreadsheet and lookup an
identifier and return a value assocated with that identifier. The problem is
if I add a new row to one of the sheets, it messes up my summary page. I
have even ensured the new row has the correct data and no blanks. What can I
do to protect the SUMPRODUCT even if I add a new row to a spreadsheet

Here's a sample of my formula
Formula lives on Summary and I add a new row to '1251 Beta' sheet

=SUMPRODUCT(('1251 Beta'!$B$10:$B$38='RESOURCE ALLOCATION'!$B61)*('1251
Beta'!C$10:C$38))