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" skrev i melding
...
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" skrev i melding
...
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
|