Formula for copying data for every 8th cell

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

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" <jbsand1001@discussions.microsoft.com> skrev i melding
news:C0A761DF-A99B-41A7-91D2-E96F2106DF53@microsoft.com...
> 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,
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" wrote:

> 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" <jbsand1001@discussions.microsoft.com> skrev i melding
> news:C0A761DF-A99B-41A7-91D2-E96F2106DF53@microsoft.com...
> > 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
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>

"jbsand1001" <jbsand1001@discussions.microsoft.com> skrev i melding
news:89FC6AD8-08F6-4AA3-A1DE-4E9E2765A93F@microsoft.com...
> 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?

If means "different from". a=b is the opposite of a<>b

> 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

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….



"jbsand1001" wrote:

> 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" wrote:
>
> > 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" <jbsand1001@discussions.microsoft.com> skrev i melding
> > news:C0A761DF-A99B-41A7-91D2-E96F2106DF53@microsoft.com...
> > > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >

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


"jbsand1001" <jbsand1001@discussions.microsoft.com> skrev i melding
news:11DFA667-8ACB-4231-848F-C3284DD5BDBA@microsoft.com...
> 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..
>
>
>
> "jbsand1001" wrote:
>
> > 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" wrote:
> >
> > > 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" <jbsand1001@discussions.microsoft.com> skrev i melding
> > > news:C0A761DF-A99B-41A7-91D2-E96F2106DF53@microsoft.com...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >