View Single Post
  #5   Report Post  
jbsand1001
 
Posts: n/a
Default

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