Formula for copying a sequence for every 8th cell
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

JBSAND1001 wrote:
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

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

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

JBSAND1001 wrote:
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