|
SUMPRODUCT formula messed up
When I use SUMPRODUCT formula, especially to look up data from
different workbooks and return to a different workbook, I tend to have problem later on when I work on it or save it to a different file. All my other formulas e.g IF, VLOOK never been messed up when I work on the file or save it to a different file. Is there anyway I could protect this SUMPRODUCT formula or any reason why it get messed up so easily? Thanks Positive |
SUMPRODUCT formula messed up
SUMPRODUCT is no different than any other functions in this regard,
you might want to give us an example where VLOOKUP using same references as SUMPRODUCT acted any differently -- Regards, Peo Sjoblom "Positive" wrote in message oups.com... When I use SUMPRODUCT formula, especially to look up data from different workbooks and return to a different workbook, I tend to have problem later on when I work on it or save it to a different file. All my other formulas e.g IF, VLOOK never been messed up when I work on the file or save it to a different file. Is there anyway I could protect this SUMPRODUCT formula or any reason why it get messed up so easily? Thanks Positive |
SUMPRODUCT formula messed up
On Jul 2, 2:47 pm, "Peo Sjoblom" wrote:
SUMPRODUCT is no different than any other functions in this regard, you might want to give us an example where VLOOKUP using same references as SUMPRODUCT acted any differently -- Regards, Peo Sjoblom "Positive" wrote in message oups.com... When I use SUMPRODUCT formula, especially to look up data from different workbooks and return to a different workbook, I tend to have problem later on when I work on it or save it to a different file. All my other formulas e.g IF, VLOOK never been messed up when I work on the file or save it to a different file. Is there anyway I could protect this SUMPRODUCT formula or any reason why it get messed up so easily? Thanks Positive- Hide quoted text - - Show quoted text - For example, I have a spreadsheet, when i insert a row, then all my SUMPRUCT formulas get messed up |
SUMPRODUCT formula messed up
That is no different from any other formulas except if you use INDIRECT in a
special way VLOOKUP behaves the same as SUMPRODUCT in this regard -- Regards, Peo Sjoblom ' "Positive" wrote in message ps.com... On Jul 2, 2:47 pm, "Peo Sjoblom" wrote: SUMPRODUCT is no different than any other functions in this regard, you might want to give us an example where VLOOKUP using same references as SUMPRODUCT acted any differently -- Regards, Peo Sjoblom "Positive" wrote in message oups.com... When I use SUMPRODUCT formula, especially to look up data from different workbooks and return to a different workbook, I tend to have problem later on when I work on it or save it to a different file. All my other formulas e.g IF, VLOOK never been messed up when I work on the file or save it to a different file. Is there anyway I could protect this SUMPRODUCT formula or any reason why it get messed up so easily? Thanks Positive- Hide quoted text - - Show quoted text - For example, I have a spreadsheet, when i insert a row, then all my SUMPRUCT formulas get messed up |
SUMPRODUCT formula messed up
On Jul 12, 2:54 pm, "Peo Sjoblom" wrote:
That is no different from any otherformulasexcept if you use INDIRECT in a special way VLOOKUP behaves the same as SUMPRODUCT in this regard -- Regards, Peo Sjoblom ' "Positive" wrote in message ps.com... On Jul 2, 2:47 pm, "Peo Sjoblom" wrote: SUMPRODUCT is no different than any other functions in this regard, you might want to give us an example where VLOOKUP using same references as SUMPRODUCT acted any differently -- Regards, Peo Sjoblom "Positive" wrote in message groups.com... When I use SUMPRODUCT formula, especially to look up data from different workbooks and return to a different workbook, I tend to have problem later on when I work on it or save it to a different file. All my otherformulase.g IF, VLOOK never been messed up when I work on the file or save it to a different file. Is there anyway I couldprotectthis SUMPRODUCT formula or any reason why it get messed up so easily? Thanks Positive- Hide quoted text - - Show quoted text - For example, I have a spreadsheet, when i insert a row, then all my SUMPRUCTformulasget messed up- Hide quoted text - - Show quoted text - Peo, Ok. How about when I insert a row, then all my VLOOKUP and SUMPRODUCT formulas get messed up? Is there a way to protect my formulas from being messed up when I edit or add, delete rows? Some messages in the GROUPs recommend MACRO but I have never use MACRO and don't really know how to start. Thanks a lot for your prompt response. |
SUMPRODUCT formula messed up
If you always want to use the same range in your formulas so if you have
=SUMPRODUCT(--($A$2:$A$100=1)) and if you insert a row at the top you don't want it to change to $A$3:$A$101? =SUMPRODUCT(--(INDIRECT("$A$2:$A$100")=1)) now there is a drawback to this since INDIRECT is a volatile function -- Regards, Peo Sjoblom "Positive" wrote in message ups.com... On Jul 12, 2:54 pm, "Peo Sjoblom" wrote: That is no different from any otherformulasexcept if you use INDIRECT in a special way VLOOKUP behaves the same as SUMPRODUCT in this regard -- Regards, Peo Sjoblom ' "Positive" wrote in message ps.com... On Jul 2, 2:47 pm, "Peo Sjoblom" wrote: SUMPRODUCT is no different than any other functions in this regard, you might want to give us an example where VLOOKUP using same references as SUMPRODUCT acted any differently -- Regards, Peo Sjoblom "Positive" wrote in message groups.com... When I use SUMPRODUCT formula, especially to look up data from different workbooks and return to a different workbook, I tend to have problem later on when I work on it or save it to a different file. All my otherformulase.g IF, VLOOK never been messed up when I work on the file or save it to a different file. Is there anyway I couldprotectthis SUMPRODUCT formula or any reason why it get messed up so easily? Thanks Positive- Hide quoted text - - Show quoted text - For example, I have a spreadsheet, when i insert a row, then all my SUMPRUCTformulasget messed up- Hide quoted text - - Show quoted text - Peo, Ok. How about when I insert a row, then all my VLOOKUP and SUMPRODUCT formulas get messed up? Is there a way to protect my formulas from being messed up when I edit or add, delete rows? Some messages in the GROUPs recommend MACRO but I have never use MACRO and don't really know how to start. Thanks a lot for your prompt response. |
SUMPRODUCT formula messed up
On Jul 12, 4:14 pm, "Peo Sjoblom" wrote:
If you always want to use the same range in your formulas so if you have =SUMPRODUCT(--($A$2:$A$100=1)) and if you insert a row at the top you don't want it to change to $A$3:$A$101? =SUMPRODUCT(--(INDIRECT("$A$2:$A$100")=1)) now there is a drawback to this since INDIRECT is a volatile function -- Regards, Peo Sjoblom "Positive" wrote in message ups.com... On Jul 12, 2:54 pm, "Peo Sjoblom" wrote: That is no different from any otherformulasexcept if you use INDIRECT in a special way VLOOKUP behaves the same as SUMPRODUCT in this regard -- Regards, Peo Sjoblom ' "Positive" wrote in message oups.com... On Jul 2, 2:47 pm, "Peo Sjoblom" wrote: SUMPRODUCT is no different than any other functions in this regard, you might want to give us an example where VLOOKUP using same references as SUMPRODUCT acted any differently -- Regards, Peo Sjoblom "Positive" wrote in message groups.com... When I use SUMPRODUCT formula, especially to look up data from different workbooks and return to a different workbook, I tend to have problem later on when I work on it or save it to a different file. All my otherformulase.g IF, VLOOK never been messed up when I work on the file or save it to a different file. Is there anyway I couldprotectthis SUMPRODUCT formula or any reason why it get messed up so easily? Thanks Positive- Hide quoted text - - Show quoted text - For example, I have a spreadsheet, when i insert a row, then all my SUMPRUCTformulasget messed up- Hide quoted text - - Show quoted text - Peo, Ok. How about when I insert a row, then all my VLOOKUP and SUMPRODUCT formulas get messed up? Is there a way to protect my formulas from being messed up when I edit or add, delete rows? Some messages in the GROUPs recommend MACRO but I have never use MACRO and don't really know how to start. Thanks a lot for your prompt response.- Hide quoted text - - Show quoted text - Peo, but hang on. When i go back to my report worksheet, it is a different case. In my report worksheet, I have SUMPRODUCT formula that gets data from different workbook. So the range is applied for a DIFFERENT WORKBOOK, which means it does not matter how many rows I add in my report ? Ex =SUMPRODUCT(--('M:\243\CLARs\CDC criticizd detail 2007\ [Port.xls]Sheet1'!$A$2:$A$271=20143385),--('M:\243\CLARs\CDC criticizd detail 2007\[Port.xls]Sheet1'!$B$2:$B$271=42),'M:\243\CLARs\CDC criticizd detail 2007\[Port.xls]Sheet1'!$V$2:$V$271)*1000+SUMPRODUCT(-- ('M:\243\CLARs\CDC criticizd detail 2007\[Port.xls]Sheet1'!$A$2:$A $271=20143385),--('M:\243\CLARs\CDC criticizd detail 2007\ [Port.xls]Sheet1'!$B$2:$B$271=34),'M:\243\CLARs\CDC criticizd detail 2007\[Port.xls]Sheet1'!$V$2:$V$271)*1000 Thanks |
| All times are GMT +1. The time now is 10:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com