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)

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)

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)

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)

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)

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)

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)

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)