Problems copying cells using offset and counta

H

hlckom

I am trying to copy selected data in every 6th cell in one row(A6) on
sheet2 and put it into a single column(A) on sheet one.

So:

1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6

becomes

6
6
6

I created the following function to do this.

=OFFSET(SHEET2!$A$6,0,(6*COUNTA(("A6:A"&((CELL("row"))-1)))))

However the count part will not work. It keeps returning nothing
instead of a count of the number of cells containing data in column
A. It is neccessary to do the "Cell" part to exclude the current cell
and so avoid getting a circular reference .

Further testing shows that it is something to do with excel not
evaluating the counta formula correctly. But I do not know why.

Any help/suggestions appreciated.
 
K

Ken Wright

Assuming your data starts in A1 on sheet 2 and you want the data from every
6th row starting row 6 in sheet2, then in sheet 1 in A1 put

=OFFSET(Sheet2!$A$1,ROW()*6-1,)

and copy down
 
K

Ken Wright

And if i read the damn question properly I might even give you the right
answer

=OFFSET(Sheet2!$A$6,,ROW()*6-1)

and copy down

Previous one assumed data was in a column, not a row.
 
M

Max

I am trying to copy selected data in every 6th cell in one row(A6) on
sheet2 and put it into a single column(A) on sheet one.
Click to expand...

One interp / way ..

Assuming data below is in row6 in Sheet2 (in A1:R1)
1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6
Click to expand...

Put in any starting cell in Sheet1, say B2:

=OFFSET(Sheet2!$A$6,0,ROWS($A$1:A1)*6-6+5)

Copy B2 down until zeros appear, signalling exhaustion of data
 
H

hlckom

And if i read the damn question properly I might even give you the right
answer

=OFFSET(Sheet2!$A$6,,ROW()*6-1)

and copy down

Previous one assumed data was in a column, not a row.
Click to expand...

Great! Adjusted it to the following and worked fine.

=OFFSET(SHEET2!$A$6,,(ROW()-6)*6)

Spent hours trying all sorts of fancy things to get this to work
new it could not be that difficult.

Thanks to both you and Max for answers
 
Top