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("ro w"))-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.