Formula for copying data for every 8th cell

[jbsand1001]

Hello,
I am looking for a formula that will allow me to copy either text or
numerical data in sequence for every 8th cell. The problem that I have is
that excel uses the relative/absolute reference and copies cells in sequence.

Example:

A1:A20=1-20 B1=1,B2="",B3=""....B8=2,B9="",B10="",B16=3

A1=1 B1=1
A2=2 B2=â€â€
A3=3….. B3=â€â€â€¦
A20=20 B8=2
B9=â€â€
B10=â€â€â€¦
B16=3


Thank You,

Judd

 

[Harald Staff]

Hi Judd

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

(Note that 1-8-16 is not every 8th)

HTH. Best wishes Harald

 

[jbsand1001]

Harald,
Thank You very much for the formula...you are correct not every
8th but nontheless you answered my question. Thanks!!!!


I would like to ask a couple of questions of how the formula works. I
understand the "If" function of the formulas but I am unfamiluar with the
following.

=if(MOD(ROW(),8)<>1,"",INDIRECT("A"&(1+INT(ROW()/8))))

MOD-- What is this telling excel to do?

()--What does this tell excel to do?

<>--What does this tell excel to do?

Indirect--What does this tell excel to do?

1+INT--What does this tell excel to do?

()/8--What does this tell excel to do?

Thank You,

Judd

 

[Harald Staff]

I would like to ask a couple of questions of how the formula works. I
understand the "If" function of the formulas but I am unfamiluar with the
following.

=if(MOD(ROW(),8)<>1,"",INDIRECT("A"&(1+INT(ROW()/8))))

MOD-- What is this telling excel to do?

Divide by n (here 8), throw the multiple of n away and return only the
remainder.

()--What does this tell excel to do?

It is an integrated part of the ROW() function

<>--What does this tell excel to do?

Indirect--What does this tell excel to do?

It creates a virtual cell address. See Help on that one.

1+INT--What does this tell excel to do?

1 and + do what they always do. INT removes decimals from a number.
INT(2.5) = 2

()/8--What does this tell excel to do?

() is part of ROW. ROW()/8 divides the row number by 8. INT removes the
decimal part of that division. Indirect uses "A" and that decimal-less
number to return a cell address. This happens only if the remaninder of row
number / 8 is 1.

HTH. Best wishes Harald

 

[jbsand1001]

Is there a way to use this formula over a 4-5 cell area to copy
numerical/text data .....for emample:


A1:A20 =1-20 B1:B20=21-40 C1:C20=41-60....

D5=1, D6=21, D7=41
D12=, D13=22, D14=42
D20=3 D21=23, D22=43....etc....

OR

A1=1 B1=21 C1=41
A2=2.. B2=22.. C2=42…… D5=1
A20=20 B20-40 C20=60 D6=21
D7=41
D8â€â€
D9â€â€
D10â€â€
D11â€â€

D12=12 D13=22
D14=42
D15â€â€â€¦.Etc….

 

[Harald Staff]

It is possible to do, but pretty awkward, and also far too dependent on
cells' absolute position. Don't, it's an awful design.

But if you insist or just want to learn, play around with
ROW()
COLUMN()
INDIRECT
INT
MOD

HTH. Best wishes Harald