Row increments -

[Janelle]

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)

 

[Pete_UK]

First of all, you can simplify your formula to this:

=IF(SUM(SheetOne!H112:J112)>40,SUM(SheetOne!H112:J112)-35,0)
=IF(SUM(SheetOne!H119:J119)>40,SUM(SheetOne!H119:J119)-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

 

[Jacob Skaria]

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

 

[Janelle]

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

 

[Janelle]

The first formula in the second sheet is in row 21, this row references row
112. Thanks.

 

[Jacob Skaria]

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

 

[Janelle]

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!

 

[T. Valko]

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)