| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row increments -
I am trying to copy a long "=IF" formula down a row by dragging it. The
"=IF" formula is linked to another worksheet, and I am trying to increase the attached cell row references (to the linked worksheet) by more than one. I can only figure out how to stop the incrementation all together, or have it increase by one each row. How can I do this? ie. =IF(SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112 40,SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112)-35,0) =IF(SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119 40,SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119)-35,0) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Row increments -
First of all, you can simplify your formula to this:
=IF(SUM(SheetOne!H112:J112)40,SUM(SheetOne!H112:J 112)-35,0) =IF(SUM(SheetOne!H119:J119)40,SUM(SheetOne!H119:J 119)-35,0) and so on. It looks like you want to increase the reference rows by 7 on each row, and you can do this using INDIRECT and the ROW() function. Which row f the second sheet contains the first formula? (and is this referring to row 112 of SheetOne?) Hope this helps. Pete On Apr 23, 5:36*pm, Janelle wrote: I am trying to copy a long "=IF" formula down a row by dragging it. *The "=IF" formula is linked to another worksheet, and I am trying to increase the attached cell row references (to the linked worksheet) by more than one. *I can only figure out how to stop the incrementation all together, or have it increase by one each row. *How can I do this? ie. =IF(SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112 40,SUM(SheetOne!H112+Shee*tOne!I112+SheetOne!J112)-35,0) =IF(SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119 40,SUM(SheetOne!H119+Shee*tOne!I119+SheetOne!J119)-35,0) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Row increments -
Please try this and once you are done you will get how to get through..
On a new workbook enter these values A12 = "One" A19 = "Two" A26 = "Three" In same sheet B1 = INDIRECT("Sheet2!A"&12+(ROW()-1)*7) Drag the formula down. Try this logic in your formula. You will have to adjust the number 12 mentioned in this formula to suit your requirements. (I am not sure in which row you have your formula ) Try and let us know...in case of help... If this post helps click Yes --------------- Jacob Skaria "Janelle" wrote: I am trying to copy a long "=IF" formula down a row by dragging it. The "=IF" formula is linked to another worksheet, and I am trying to increase the attached cell row references (to the linked worksheet) by more than one. I can only figure out how to stop the incrementation all together, or have it increase by one each row. How can I do this? ie. =IF(SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112 40,SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112)-35,0) =IF(SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119 40,SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119)-35,0) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Row increments -
I am having difficulty applying this example to my particular workbook. My
formula starts on row 21 of the second sheet..how do I incorporate that into the formula that you gave me? Thanks "Jacob Skaria" wrote: Please try this and once you are done you will get how to get through.. On a new workbook enter these values A12 = "One" A19 = "Two" A26 = "Three" In same sheet B1 = INDIRECT("Sheet2!A"&12+(ROW()-1)*7) Drag the formula down. Try this logic in your formula. You will have to adjust the number 12 mentioned in this formula to suit your requirements. (I am not sure in which row you have your formula ) Try and let us know...in case of help... If this post helps click Yes --------------- Jacob Skaria "Janelle" wrote: I am trying to copy a long "=IF" formula down a row by dragging it. The "=IF" formula is linked to another worksheet, and I am trying to increase the attached cell row references (to the linked worksheet) by more than one. I can only figure out how to stop the incrementation all together, or have it increase by one each row. How can I do this? ie. =IF(SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112 40,SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112)-35,0) =IF(SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119 40,SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119)-35,0) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Row increments -
The first formula in the second sheet is in row 21, this row references row
112. Thanks. "Pete_UK" wrote: First of all, you can simplify your formula to this: =IF(SUM(SheetOne!H112:J112)40,SUM(SheetOne!H112:J 112)-35,0) =IF(SUM(SheetOne!H119:J119)40,SUM(SheetOne!H119:J 119)-35,0) and so on. It looks like you want to increase the reference rows by 7 on each row, and you can do this using INDIRECT and the ROW() function. Which row f the second sheet contains the first formula? (and is this referring to row 112 of SheetOne?), Hope this helps. Pete On Apr 23, 5:36 pm, Janelle wrote: I am trying to copy a long "=IF" formula down a row by dragging it. The "=IF" formula is linked to another worksheet, and I am trying to increase the attached cell row references (to the linked worksheet) by more than one. I can only figure out how to stop the incrementation all together, or have it increase by one each row. How can I do this? ie. =IF(SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112 40,SUM(SheetOne!H112+SheeÂ*tOne!I112+SheetOne!J112 )-35,0) =IF(SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119 40,SUM(SheetOne!H119+SheeÂ*tOne!I119+SheetOne!J119 )-35,0) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Row increments -
Janelle, try the below...If you copy it down it should increment by 7..
=INDIRECT("Sheet2!A"& 112+(ROW()-21)*7) If this post helps click Yes --------------- Jacob Skaria "Janelle" wrote: I am having difficulty applying this example to my particular workbook. My formula starts on row 21 of the second sheet..how do I incorporate that into the formula that you gave me? Thanks "Jacob Skaria" wrote: Please try this and once you are done you will get how to get through.. On a new workbook enter these values A12 = "One" A19 = "Two" A26 = "Three" In same sheet B1 = INDIRECT("Sheet2!A"&12+(ROW()-1)*7) Drag the formula down. Try this logic in your formula. You will have to adjust the number 12 mentioned in this formula to suit your requirements. (I am not sure in which row you have your formula ) Try and let us know...in case of help... If this post helps click Yes --------------- Jacob Skaria "Janelle" wrote: I am trying to copy a long "=IF" formula down a row by dragging it. The "=IF" formula is linked to another worksheet, and I am trying to increase the attached cell row references (to the linked worksheet) by more than one. I can only figure out how to stop the incrementation all together, or have it increase by one each row. How can I do this? ie. =IF(SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112 40,SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112)-35,0) =IF(SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119 40,SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119)-35,0) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Row increments -
Hey,
I'm close to figuring this beast out but i'm running into a jam when it comes to incorporating the sum of three cells. Instead of referencing only H112 I would like to reference the sum of H112 + I112 + J112. I'm getting lost in brackets and colons. This is the formula I have that works for me, properly incrementing by 7 each time. Any thoughts on how to incorporate the SUM function? =IF((INDIRECT("Mike!H"&112+(ROW()-21)*7))35,(INDIRECT("Mike!$H"&112+((ROW()-21)*7)))-35,0) Thanks for the help! "Pete_UK" wrote: First of all, you can simplify your formula to this: =IF(SUM(SheetOne!H112:J112)40,SUM(SheetOne!H112:J 112)-35,0) =IF(SUM(SheetOne!H119:J119)40,SUM(SheetOne!H119:J 119)-35,0) and so on. It looks like you want to increase the reference rows by 7 on each row, and you can do this using INDIRECT and the ROW() function. Which row f the second sheet contains the first formula? (and is this referring to row 112 of SheetOne?) Hope this helps. Pete On Apr 23, 5:36 pm, Janelle wrote: I am trying to copy a long "=IF" formula down a row by dragging it. The "=IF" formula is linked to another worksheet, and I am trying to increase the attached cell row references (to the linked worksheet) by more than one. I can only figure out how to stop the incrementation all together, or have it increase by one each row. How can I do this? ie. =IF(SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112 40,SUM(SheetOne!H112+SheeÂ*tOne!I112+SheetOne!J112 )-35,0) =IF(SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119 40,SUM(SheetOne!H119+SheeÂ*tOne!I119+SheetOne!J119 )-35,0) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Row increments -
Try this...
Table is a named range that refers to Mike!$H$112:$J$500. Adjust for the correct end of the range. Assume the firt result is to appear in cell A21. Enter this formula in A21 and copy down as needed: =IF(SUM(INDEX(Table,ROWS(A$21:A21)*7-7+1,0))40,SUM(INDEX(Table,ROWS(A$21:A21)*7-7+1,0))-35,0) -- Biff Microsoft Excel MVP "Janelle" wrote in message ... Hey, I'm close to figuring this beast out but i'm running into a jam when it comes to incorporating the sum of three cells. Instead of referencing only H112 I would like to reference the sum of H112 + I112 + J112. I'm getting lost in brackets and colons. This is the formula I have that works for me, properly incrementing by 7 each time. Any thoughts on how to incorporate the SUM function? =IF((INDIRECT("Mike!H"&112+(ROW()-21)*7))35,(INDIRECT("Mike!$H"&112+((ROW()-21)*7)))-35,0) Thanks for the help! "Pete_UK" wrote: First of all, you can simplify your formula to this: =IF(SUM(SheetOne!H112:J112)40,SUM(SheetOne!H112:J 112)-35,0) =IF(SUM(SheetOne!H119:J119)40,SUM(SheetOne!H119:J 119)-35,0) and so on. It looks like you want to increase the reference rows by 7 on each row, and you can do this using INDIRECT and the ROW() function. Which row f the second sheet contains the first formula? (and is this referring to row 112 of SheetOne?) Hope this helps. Pete On Apr 23, 5:36 pm, Janelle wrote: I am trying to copy a long "=IF" formula down a row by dragging it. The "=IF" formula is linked to another worksheet, and I am trying to increase the attached cell row references (to the linked worksheet) by more than one. I can only figure out how to stop the incrementation all together, or have it increase by one each row. How can I do this? ie. =IF(SUM(SheetOne!H112+SheetOne!I112+SheetOne!J112 40,SUM(SheetOne!H112+Shee*tOne!I112+SheetOne!J112)-35,0) =IF(SUM(SheetOne!H119+SheetOne!I119+SheetOne!J119 40,SUM(SheetOne!H119+Shee*tOne!I119+SheetOne!J119)-35,0) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula with increments of 6 | Excel Worksheet Functions | |||
| increments | Excel Discussion (Misc queries) | |||
| Time increments | New Users to Excel | |||
| ROUNDUP IN 0.2 INCREMENTS | Excel Worksheet Functions | |||
| How do I add time when some of the increments are less than an ho | New Users to Excel | |||
Linear Mode
