Fetching data from another worksheet

Hi

I m creating a worksheet where I have to bring the data from another worksheet and add for 7 cells.

e.g. worksheet w1 cell a1 has =sum(W2!a1..a7)
a2 should have =sum(W2!a8..a14)
a3 should have =sum(W2!a15..a21) and so on

when I write the formula in A1 and copy or extend it to A2 its taking A2 as starting cell instead of A8.

Help please! how do I copy the formula so it will take =sum(w2!a1..a7) in a1 and =sum(w2!a8..a14) in a2 and =sum(w2!a15..a21) in a3 and so on.

Try

=SUM(INDEX(W2!$A$1:$A$100,1+((ROW(W2!A1)-1)*7)):INDEX(W2!$A$1:$A$100,7+((ROW(W2!A1)-1)*7)))

adjust the 100 to match the bottom of your entire range...

Hi,

In A1 and copied down.

Please Login or Register  to view this content.

Rgds

Originally Posted by NBVC

Try

=SUM(INDEX(W2!$A$1:$A$100,1+((ROW(W2!A1)-1)*7)):INDEX(W2!$A$1:$A$100,7+((ROW(W2!A1)-1)*7)))

adjust the 100 to match the bottom of your entire range...

this might work as I have changed the W2 with the real worksheet name and A1 as B152 and A100 as IV152 so it COLUMN instead of rows.

Summing up first 7 cells of 152nd row in the first cell in summary tab and 2nd cell will be next 7 cell's sum.

I modified the formula as =SUM(INDEX(Art!$B$152:$IV$152,1+((COLUMN(Art!B152)-1)*7)):INDEX(Art!$B$152:$IV$152,7+((COLUMN(Art!B152)-1)*7)))

gives nothing and I HAVE the data in Art sheet

Try:

=SUM(INDEX(Art!$B$152:$IV$152,1+((COLUMN(Art!B152)-COLUMN(Art!$B2))*7)):INDEX(Art!$B$152:$IV$152,7+((COLUMN(Art!B152)-COLUMN(Art!$B2))*7)))

or

=SUM(INDEX(Art!$B$152:$IV$152,1+((COLUMNS(Art!$B152:Art!B152)-1)*7)):INDEX(Art!$B$152:$IV$152,7+((COLUMNS(Art!$B152:Art!B152)-1)*7)))