Copy formulas down that skip

[clafever]

Open excel. Input the following.<o></o>
<o> </o>
CellA1 put =j33<o></o>
CellA2 put =j48<o></o>
CellA3 put =j63<o></o>
<o> </o>
Now…I want the rest of the column down to continue in the same sequence adding 15 to each cell reference. Yes, I've tried dragging and copying and various formats. A4 should =j78 A5 should be =j93 and so on. I’ve tried everything that doesn’t work. If you were doing this with values it works perfectly but I can’t seem to do itwith this simple formula. You can also play with =j1 =j3 =j5 It works with =j1 =j2 =j3 fine. There is some sort of weird loop. The application I'm using this for is actually pretty complicated referring to other workbooks/sheets and merging but this is broken down to the simplest form of the problem.<o></o>
<o> </o>
Help! <o></o>

 

[Jonmo1]

welcome to the board..

try this in A1 and filled down

=INDEX(J:J,33+((ROWS(A$1:A1)-1)*15))

33 represents the First Row to reference
15 represents the interval..

 

[meldoc]

hello

In your example select both A2 and A3 and copy down as far as you need, this will do what you want.

 

[Jonmo1]

hello

In your example select both A2 and A3 and copy down as far as you need, this will do what you want.

No it won't...

It will end up like this

=j33
=j48
=j35
=j50
=j37
=j52

Actually only incrementing each formula by 2 rows.

 

[clafever]

meldoc: thank you but it's not that simple. Try it and you will see the problem.

Jonmo1: Wow, this works great in the example. I wouldn't have figure that out in awhile. Now for the next part. I've got to make it work with the references I'm using. I'll try and paste it. I'll try it but you will probably have it done before I do Thanks!

<table x:str="" style="border-collapse: collapse; width: 524pt;" width="698" border="0" cellpadding="0" cellspacing="0"><col style="width: 524pt;" width="698"> <tbody><tr style="height: 15.95pt;" height="21"> <td class="xl24" style="height: 15.95pt; width: 524pt;" x:str=",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,," width="698" height="21">='G:\Cargo Packaging WS\[CargoPackagingNorth2010Stock.xls]Jan'!J48&","&'G:\Cargo Packaging WS\[CargoPackagingNorth2010DVD.xls]Jan'!K48</td> </tr> <tr><td style="vertical-align: top;">
</td></tr><tr style="height: 15.95pt;" height="21"> <td class="xl24" style="height: 15.95pt;" x:str=",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,," height="21">='G:\Cargo Packaging WS\[CargoPackagingNorth2010Stock.xls]Jan'!J63&'G:\Cargo Packaging WS\[CargoPackagingNorth2010DVD.xls]Jan'!K63</td> </tr> <tr style="height: 15.95pt;" height="21"> <td class="xl24" style="height: 15.95pt;" x:str=",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,," height="21">
='G:\Cargo Packaging WS\[CargoPackagingNorth2010Stock.xls]Jan'!J78&","&'G:\Cargo Packaging WS\[CargoPackagingNorth2010DVD.xls]Jan'!K78</td> </tr> <tr style="height: 15.95pt;" height="21"> <td class="xl25" style="border-top: medium none; height: 15.95pt;" x:str=",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,," height="21">
='G:\Cargo Packaging WS\[CargoPackagingNorth2010Stock.xls]Jan'!J93&","&'G:\Cargo Packaging WS\[CargoPackagingNorth2010DVD.xls]Jan'!K93</td> </tr> </tbody></table>
<table x:str="" style="border-collapse: collapse; width: 262pt;" width="349" border="0" cellpadding="0" cellspacing="0"><col style="width: 262pt;" width="349"> <tbody><tr style="height: 15.95pt;" height="21"> <td class="xl24" style="height: 15.95pt; width: 262pt;" width="349" height="21">
</td></tr><tr style="height: 15.95pt;" height="21"><td class="xl24" style="height: 15.95pt;" height="21">
</td> </tr> <tr style="height: 15.95pt;" height="21"> <td class="xl24" style="height: 15.95pt;" height="21">
</td> </tr> <tr style="height: 15.95pt;" height="21"> <td class="xl25" style="border-top: medium none; height: 15.95pt;" height="21">
</td> </tr> </tbody></table>

 

[Jonmo1]

=INDEX(J:J,48+((ROWS(A$1:A1)-1)*15))&INDEX(K:K,48+((ROWS(A$1:A1)-1)*15))

All you have to do is add the path\bookname\sheetname to the bold red parts.
No need to add those refs to the Rows(..) part..

 

[clafever]

OK, thanks. I'll work with that for a bit. I think I've got it. I like to figure these things out on my own but couldn't on this one.

 

[meldoc]

Hello again

Yes sorry I gave you something that you had already rejected.
I must learn to read things through.

 

[clafever]

This is what I've come up with. Works great, thanks for pointing me in the right direction. Array/ index was the key I was missing.


=INDEX([CargoPackagingNorth2010Stock.xls]Jan!J:J,33+((ROWS(A$1:A1)-1)*15))&","&INDEX([CargoPackagingNorth2010DVD.xls]Jan!K:K,33+((ROWS(A$1:A1)-1)*15))