Copy Paste Reference Formulas (but need it to skip)

Posted by CPU Nut on October 13, 2001 9:15 AM

I've always thought I knew a bit about Excel, but this one has me stumped. I am trying to copy and paste a formula into the next several 1,000) rows. The formula refers to another worsheet.

=+'Other Tab'!D6

The next cell down that I copy this into, if I just use copy and paste, will automatically changes the formula to

=+'Other Tab'!D7

I need it to say

=+'Other Tab'!D9

I basically want it to skip 2 lines for every cell I paste it into in sequence. I could copy and paste down a 1,000 rows and go back and delete every other 2 lines, but there's gotta be a better way. Please email me if you need me to send you what I am working on. Its a real simple worksheet but it will be alot of work otherwise. appreciate the help!! I actually have another one that I'll ask in the near future!!! I'll try and help others if I can, I'm new to this board.

Posted by Jonathan on October 13, 2001 11:39 AM

This will produce the sequence D6, D9, D12 and so on:

=TEXT("D"&ROW(D3)+2*ROW(D2)-1,"@")

Perhaps you can fit this into your formula to make it work for you.

HTH

Posted by David Kelly on October 13, 2001 12:06 PM

Sent email to you

Posted by Nandor Hidegkuti on October 13, 2001 12:39 PM

=INDIRECT("D"&ROW(D3)+2*ROW(D2)-1)

Posted by CPU Nut on October 13, 2001 12:42 PM

OKAY,

The good news is I am learning alot. Bad news is I'm just not catching on to solving my problems. I have applied the suggestions I have recieved and I am feeling that I did not explain enough info for you guys. And that is my fault. Anyone willing to give me an email address and I will give you a sampling of what I got on both or either of my problems.
Thanks for trying and bare with me!!!!
CPU Nut

Posted by Aladin Akyurek on October 13, 2001 1:17 PM

In A1 in the target worksheet enter: ='Other Tab'!D6
In A2 in the target worksheet enter: =IF(COUNT('Other Tab'!$D:$D)>=3*COUNT($A$1:A1)+6,OFFSET('Other Tab'!$D$6,3*COUNT($A$1:A1),,),"")

Copy the formula down as needed (by using AutoFill handle).

Aladin

========= I've always thought I knew a bit about Excel, but this one has me stumped. I am trying to copy and paste a formula into the next several 1,000) rows. The formula refers to another worsheet.