MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

ItÂ’s 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.