I did not think that my "Problem linking contiguous cells" posted on 6/29/2000 is a challenge even f

Posted by Al P on July 12, 2000 3:26 PM

There is a way to build a formula (not macro) to solve the following problem? This is just a short example, in fact the range is much larger.

I have numbers in range A1:A6: A1=2; A2=4; A3=55; A4=6; A5=12; A6=32
I want to link these cells with every other 10th cell in column D.

D1 has =A1; D11 has =A2; D21 has =A3; D31 has =A4; D41 has =A5;
D51 has =A6

Its easy to enter the link formula when I have just a few cells in my range, but
for about 100 is kind of tedious. I was trying to build in D1 a formula (something
with Offset function) and after that to select/copy D1:D10 and paste in D11 to
get A2, and after that select D1:D20 and paste in D21 (getting A3 and A4) a.s.o.
Using this approach after a couple of copy/paste the range will be larger and it
will save time versus doing one link by one.

Posted by Ada on July 13, 0100 2:54 AM

Perhaps more flexibility is required

A1 P
A macro solution is simple but you are insisting on a formula solution - which is apparently not so simple.
I can't follow your logic for requiring a formula solution only - there is usually more than one way to skin a cat!
Ada

Posted by AB on July 13, 0100 6:14 AM

Two steps...

1. Enter this formula in cell [D1]
=OFFSET(A$1,(ROW()-1)/10,0)

2. Select cells [D1:D10] (that's 1 formula and 9 empty cells) and pull the autofill handle down as far as you want to copy the formula for every tenth cell.

-Aaron :)

Posted by Ada on July 13, 0100 7:19 AM

Nice one!

Nice one Aaron! No problem for a real expert.
Ada

Posted by Al P on July 13, 0100 8:03 AM

Thank you AB, it works, thanks so much

It works beautifully.