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)