Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting rows messing up formulas | Excel Discussion (Misc queries) | |||
Tab character is messing me up | Excel Discussion (Misc queries) | |||
Sort without messing up formulas | Excel Discussion (Misc queries) | |||
Sort & add columns without messing up formulas | Excel Worksheet Functions | |||
Rows containing "#N/A" are messing with my formulas, please help | Excel Worksheet Functions |