Formula for copying a sequence for every 8th cell

[JBSAND1001]

Hello,
I am looking for a formula that will copy data either text or
numerical data in a sequence but for every 8th cell. I keep running into the
problem of excel using the abosulte/relative reference to capture the string.
For example

Cell A1:A20 = 1-20 I want to capture in sequence 1-20 in column B except
every 8th cell.

A1=1 B1=1
A2=2 B2=""
A3=3.... B3="".....
A20=20 B8=2
B9=""
B10=""....
B16=3

 

[Frank Kabel]

Hi
in B1 enter
=IF(MOD(ROW()-1,8)=0,OFFSET($A$1,INT((ROW()-1)/8),0),"")
and copy down.

NOTE: every 8th cell means the sequence:
B1
B9
B17
....

and NOT
B1
B8
B16

 

[Andy Brown]

A1=1 B1=1

A2=2 B2=""
A3=3.... B3="".....
A20=20 B8=2
B9=""
B10=""....
B16=3

Strictly speaking that's not every 8th row. That aside, try something like

=IF(ROW()=1,$A$1,IF(MOD(ROW(),8)<>0,"",INDIRECT("A"&(ROW()/8)+1)))

HTH,
Andy

 

[Aladin Akyurek]

=INDEX($B$1:$B$100,(ROW()-ROW($A$1))*8+1)