Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#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 |
#4
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
using content of a cell in a formula in another cell | Excel Worksheet Functions | |||
How can I write an if-then formula for 0 or less than 0 in cell t. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |