Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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("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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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. |
#4
|
|||
|
|||
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 <atyahoo<dotcom ---- "hlckom" wrote in message ... 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("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. |
#5
|
|||
|
|||
On Sat, 29 Jan 2005 22:11:59 -0000, "Ken Wright"
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|