| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Chart gets messed up after zooming | Charts and Charting in Excel | |||
| Lookup really messed up | Excel Discussion (Misc queries) | |||
| I messed up and need help | New Users to Excel | |||
| VLOOKUP being messed up by formating? Help.. | Excel Discussion (Misc queries) | |||
| Linking messed up when Sorting | Excel Discussion (Misc queries) | |||
Linear Mode
