Problems copying cells using offset and counta

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.

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"hlckom" <rubbish@nothing12.com> wrote in message
news:2munv0purf13t8lk1fldbl31fludhp551o@4ax.com...
> 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.
>
>
>
>

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:%23HN0x7kBFHA.3616@TK2MSFTNGP11.phx.gbl...
> 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
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "hlckom" <rubbish@nothing12.com> wrote in message
> news:2munv0purf13t8lk1fldbl31fludhp551o@4ax.com...
> > 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.
> >
> >
> >
> >
>
>

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

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

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"hlckom" <rubbish@nothing12.com> wrote in message
news:2munv0purf13t8lk1fldbl31fludhp551o@4ax.com...
> 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:
>

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

On Sat, 29 Jan 2005 22:11:59 -0000, "Ken Wright"
<ken.wright@NOSPAMntlworld.com> wrote:

>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.
>
>--
>Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
>----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
>----------------------------------------------------------------------------
>
>"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
>news:%23HN0x7kBFHA.3616@TK2MSFTNGP11.phx.gbl...
>> 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
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> --------------------------------------------------------------------------
>--
>> It's easier to beg forgiveness than ask permission :-)
>> --------------------------------------------------------------------------

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