ExcelBanter - autofill series

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - autofill series (https://www.excelbanter.com/excel-worksheet-functions/143435-autofill-series.html)

Excel 2003
Sheet 1: I have data in A1, A2, A3,A4,A5 ... A365
On Sheet 2, I need to copy the data from every 7th row on sheet 1 and would
like to do this with autofill.

I would appreciate any help.
--
Guido

Every 7 rows, starting at A1 would be:
A1, A8, A15, A22 ... etc.

Enter this *anywhere*, and copy down:

=INDEX(Sheet1!A:A,7*ROWS($1:1)-6)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Guido Cole" wrote in message
...
Excel 2003
Sheet 1: I have data in A1, A2, A3,A4,A5 ... A365
On Sheet 2, I need to copy the data from every 7th row on sheet 1 and
would
like to do this with autofill.

I would appreciate any help.
--
Guido

Sun, 20 May 2007 10:50:00 -0700 from Guido Cole
:
Excel 2003
Sheet 1: I have data in A1, A2, A3,A4,A5 ... A365
On Sheet 2, I need to copy the data from every 7th row on sheet 1 and would
like to do this with autofill.

Guido,

It's not clear to me whether you want to (a) put data in sheet 2 A1,
A8, A15, A22 etc. or (b) put data into sheet2 A1:A52 (every seventh
row of sheet 1 collapsed into sheet 2).

Could you clarify, please?

And if you do want (a) then what is to go in sheet 2 A2:A7, A9:A14,
A16:A21 etc.?

If you want (b), here's how. Put this in sheet2 A1:
=OFFSET(sheet1!A$1,7*(ROW()-1),0)
Then click and drag through A52. If you're setting up your array in
sheet 2 starting somewhere other than row 1, change the "-1" in the
formula to minus your starting row number before you click and drag
down 52 rows.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Thank you. It is "b". Would you know what the equivalent of the offset
function is in spanish?
--
Guido

"Stan Brown" wrote:

Sun, 20 May 2007 10:50:00 -0700 from Guido Cole
:
Excel 2003
Sheet 1: I have data in A1, A2, A3,A4,A5 ... A365
On Sheet 2, I need to copy the data from every 7th row on sheet 1 and would
like to do this with autofill.

Guido,

It's not clear to me whether you want to (a) put data in sheet 2 A1,
A8, A15, A22 etc. or (b) put data into sheet2 A1:A52 (every seventh
row of sheet 1 collapsed into sheet 2).

Could you clarify, please?

And if you do want (a) then what is to go in sheet 2 A2:A7, A9:A14,
A16:A21 etc.?

If you want (b), here's how. Put this in sheet2 A1:
=OFFSET(sheet1!A$1,7*(ROW()-1),0)
Then click and drag through A52. If you're setting up your array in
sheet 2 starting somewhere other than row 1, change the "-1" in the
formula to minus your starting row number before you click and drag
down 52 rows.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

I believe it's
DESREF

The Index formula ( INDICE ) produces the same results, plus, it's
non-volatile.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Guido Cole" wrote in message
...
Thank you. It is "b". Would you know what the equivalent of the offset
function is in spanish?
--
Guido

"Stan Brown" wrote:

Sun, 20 May 2007 10:50:00 -0700 from Guido Cole
:
Excel 2003
Sheet 1: I have data in A1, A2, A3,A4,A5 ... A365
On Sheet 2, I need to copy the data from every 7th row on sheet 1 and
would
like to do this with autofill.

Guido,

It's not clear to me whether you want to (a) put data in sheet 2 A1,
A8, A15, A22 etc. or (b) put data into sheet2 A1:A52 (every seventh
row of sheet 1 collapsed into sheet 2).

Could you clarify, please?

And if you do want (a) then what is to go in sheet 2 A2:A7, A9:A14,
A16:A21 etc.?

If you want (b), here's how. Put this in sheet2 A1:
=OFFSET(sheet1!A$1,7*(ROW()-1),0)
Then click and drag through A52. If you're setting up your array in
sheet 2 starting somewhere other than row 1, change the "-1" in the
formula to minus your starting row number before you click and drag
down 52 rows.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Guido

Eric Desart has written an add-in for translating worksheet function names into
11 languages, Spanish being one of those.

http://www.rondebruin.nl/atptranslator.htm

Note: not just the ATP translations, but all worksheet functions.

Gord Dibben MS Excel MVP

On Sun, 20 May 2007 11:52:00 -0700, Guido Cole wrote:

Thank you. It is "b". Would you know what the equivalent of the offset
function is in spanish?

Thanks to everybody, the index function worked perfectly.
--
Guido

"Ragdyer" wrote:

I believe it's
DESREF

The Index formula ( INDICE ) produces the same results, plus, it's
non-volatile.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Guido Cole" wrote in message
...
Thank you. It is "b". Would you know what the equivalent of the offset
function is in spanish?
--
Guido

"Stan Brown" wrote:

Sun, 20 May 2007 10:50:00 -0700 from Guido Cole
:
Excel 2003
Sheet 1: I have data in A1, A2, A3,A4,A5 ... A365
On Sheet 2, I need to copy the data from every 7th row on sheet 1 and
would
like to do this with autofill.

Guido,

It's not clear to me whether you want to (a) put data in sheet 2 A1,
A8, A15, A22 etc. or (b) put data into sheet2 A1:A52 (every seventh
row of sheet 1 collapsed into sheet 2).

Could you clarify, please?

And if you do want (a) then what is to go in sheet 2 A2:A7, A9:A14,
A16:A21 etc.?

If you want (b), here's how. Put this in sheet2 A1:
=OFFSET(sheet1!A$1,7*(ROW()-1),0)
Then click and drag through A52. If you're setting up your array in
sheet 2 starting somewhere other than row 1, change the "-1" in the
formula to minus your starting row number before you click and drag
down 52 rows.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/