Dragging formula's

Hello.

I am trying to reference data from another sheet, and then just drag the cell down that normally just adjusts the formula accordingly.

However the data I'm trying to reference, is horizontal and not vertical on the other sheet, so for example I'm trying to reference formulas;

=SUM(ROTA!C16:I16)
=SUM(ROTA!J16:P16)
=SUM(ROTA!Q16:W16)

If I have to do this, I have to do it 52 times, 4 times. When I just drag the formula down it adjusts to the below, moving it down horizontal.

=SUM(ROTA!C16:I16)
=SUM(ROTA!C17:I17)
=SUM(ROTA!C18:I18)

I'd appreciate any help, as I'd be stuck here all day as I have to do this a few times.

You can use this formula for your first range:

=SUM(INDEX(ROTA!$16:$16,(ROWS($1:1)-1)*7+3):INDEX(ROTA!$16:$16,ROWS($1:1)*7+2))

then copy down as required.

Hope this helps.

Pete

Originally Posted by Special-K

... but blowed if I can see a simple solution

Mine's quite simple !!

Pete

I've attached an example, hope this helps?

Thanks in advance

Amending my formula to suit your (different) sheet names, put this in B3 of Sheet2:

=SUM(INDEX(Sheet1!$16:$16,(ROWS($1:1)-1)*7+3):INDEX(Sheet1!$16:$16,ROWS($1:1)*7+2))

then copy down. I don't know what your other headings in that sheet are meant to be, but perhaps you can use the same formula and change the $16 references to $23 and $26 in those other columns.

Hope this helps.

Pete