Problem linking contiguous cells to noncontiguous cells.
Posted by Al P on June 29, 2000 6:17 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 Patrick on July 21, 0100 4:06 AM
This isn't pretty, but it works.
=IF(RIGHT(TEXT(ROW(D2),0),1)="1",OFFSET(D2,-9*(VALUE(LEFT(TEXT(ROW(E2),0),LEN(ROW(E2))-1))),-3,1,1),"")
Hope you find something simpler.
Cheers
Patrick
Posted by Al P on July 05, 0100 11:51 AM
Please please can anyone solve this Excel problem?????
Can anyone advice how to solve this problem? Thank you.
Posted by mads on July 05, 0100 11:55 AM
Re: Please please can anyone solve this Excel problem?????
Why do you not want to do it with a macro?
mads
Posted by Al P on July 05, 0100 1:23 PM
Re: Please please can anyone solve this Excel problem?????
My users are not very proficient with Excel and I already have more macros on the same workbook.
