Enter every nth cell item
 

Hello,
Using Excel 2003. I am looking for a formula that will grab every 4th item
in a column and then paste it to a new column (or worksheet). For example,
cells A1, A2, ... A20 contain first names. I want to grab every 4th name in
the column and be copied to column D. Thanks for your help.
--
sdm

Hi
in D1 enter
=OFFSET($A$1,(ROW()-1)*4,0)
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany

sdm wrote:
Hello,
Using Excel 2003. I am looking for a formula that will grab every 4th
item in a column and then paste it to a new column (or worksheet).
For example, cells A1, A2, ... A20 contain first names. I want to
grab every 4th name in the column and be copied to column D. Thanks
for your help.

Although you can do this with VBA you can also do it using a formula

if you meant extract what's in A1, A5, A9 and so on


=OFFSET($A$1,ROW(1:1)*4-4,)

copy down

if you meant A4, A8, A12 and so on

=OFFSET($A$1,ROW(1:1)*4-1,)


Regards,

Peo Sjoblom


"sdm" wrote:

Hello,
Using Excel 2003. I am looking for a formula that will grab every 4th item
in a column and then paste it to a new column (or worksheet). For example,
cells A1, A2, ... A20 contain first names. I want to grab every 4th name in
the column and be copied to column D. Thanks for your help.
--
sdm

Thanks, Frank for you quick response. Works great!
Sonya

"Frank Kabel" wrote:

Hi
in D1 enter
=OFFSET($A$1,(ROW()-1)*4,0)
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany

sdm wrote:
Hello,
Using Excel 2003. I am looking for a formula that will grab every 4th
item in a column and then paste it to a new column (or worksheet).
For example, cells A1, A2, ... A20 contain first names. I want to
grab every 4th name in the column and be copied to column D. Thanks
for your help.

If entered in D1...

=INDEX(A:A,(ROW()-ROW($D$1))*4+1)

which starts copying with the item in A1.

=INDEX(A:A,(ROW()-ROW(D$1))*4+4)

which starts copying with the item in A4.

These formulas are robust against inserting rows before the data and/or
before the formula cell.

"sdm" wrote in message
...
Hello,
Using Excel 2003. I am looking for a formula that will grab every 4th item
in a column and then paste it to a new column (or worksheet). For example,
cells A1, A2, ... A20 contain first names. I want to grab every 4th name
in
the column and be copied to column D. Thanks for your help.
--
sdm