Thread: 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.

Code:

=SUM(INDIRECT("'W2'!A"&ROW()*7-6&":A"&ROW()*7))

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)))