Cell reference equation??

[MrSandman]

I've got a worksheet which has data all in one column but in every 5th
row. I'd like to copy the data over to another column so that the data
does not skip rows. In otherwords, the blanks are removed. How can i
do this? Is there an equation of some sort that i can have Excel copy
the data from every 5th cell? Please help!!!

 

[Ken Wright]

Select the entire column, do Edit / Go To / Special / Blanks, then hit Edit /
Delete / Entire Row.

 

[MrSandman]

Thx for the response. Now lets say that the cells between them are no
blank. How would i extract this data? Will i have to run a macro

 

[Aladin Akyurek]

A bit fancy, although not necessarily inefficient...

Let A2:A28 house:

{"data";42;56;50;46;64;37;70;62;66;62;63;49;67;46;70;54;50;65;51;43;69;65;58
;40;77;90}

In B2 enter: 0 [required]

In B3 enter & copy down:

=IF((A3<>"")*INDEX(MOD(ROW($A$3:$A$28)-CELL("Row",$A$3)+0,5)=0,ROW()-ROW($A$
3)+1),LOOKUP(9.99999999999999E+307,$B$2:B2)+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,$B$3:$B$28)

In C3 enter & copy down (in fact, this copying should better be done with
code in VBA that refers to C1)

=IF(ROW()-ROW($C$3)+1<=$C$1,INDEX($A$3:$A$28,MATCH(ROW()-ROW($C$3)+1,$B$3:$B
$28,0)),"")

The result area will look like this:

{6;"list";42;37;63;54;69;90}

 

[Frank Kabel]

Hi
try the following formula in your second sheet (assumption column a
stores your values starting in row 1):
=OFFSET('sheet1'!$A$1,(ROW()-1)*5,0)
and copy this down