Copy formulas down that skip

clafever

New Member
Joined
Dec 22, 2009
Messages
4
Open excel. Input the following.<o:p></o:p>
<o:p> </o:p>
CellA1 put =j33<o:p></o:p>
CellA2 put =j48<o:p></o:p>
CellA3 put =j63<o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
Help! <o:p></o:p>
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
hello

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Dec 22, 2009
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
=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

New Member
Joined
Dec 22, 2009
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
Hello again

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

clafever

New Member
Joined
Dec 22, 2009
Messages
4
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top