Fill series

JoshuaMars

New Member
Joined
Jul 13, 2006
Messages
46
Hi there Excel users,

Im sure there is an easy reason why.

Im trying to do a fill series of a column but it doesnt seem to work.

Eg.

A19 ='Daily Totals'!C169
A20 ='Daily Totals'!C180
A21 ='Daily Totals'!C191
A22 ='Daily Totals'!C202

etc etc etc

But if I try to do a fill series of this column down say the next 2 rows i get this result.

A23 ='Daily Totals'!C193
A24 ='Daily Totals'!C204

Help, what am I doing wrong?

Im trying to make it number raise by 11 but its seems to have droped by 9 first then rasies by 11.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Rastaman

Board Regular
Joined
Feb 18, 2006
Messages
236
Marsy,
Looks like you dragged the last two cells down. C191 dragged down 2 rows becomes C193, C202 becomes C204. Pretty much just what Excel is supposed to do. When dragging formulas Excel follows cell reference rules. If you were dragging a numeric series you could get a series incremented by 11. One way to solve this is to use the indirect command.

This example sums every 11th row per your example. Copy this formula to cell A19 and drag down.

=INDIRECT("'Daily Totals'!C"&ROW()*11-40)

Lots of other ways depending on which rows you want to reference and where your starting point is. You could use a helper column or modify the formula above.

Rick
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
As an alternative, you can construct a formula that'll do the same thing...

=INDEX(Sheet3!C:C,158+(11*(ROW(A1))))
 

JoshuaMars

New Member
Joined
Jul 13, 2006
Messages
46
PaddyD,

Your formula seems like it might work.

Could you explain your formula please.

Thanks in advance.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
you can check the excel help file for how index() works. the

158+(11*(ROW(A1))))

...remembering that row(a1) returns 1, row(a2) returns 2 etc, it starts at 158, and adds (11*n) to it, then feeds that to the row argument of the index()
 

Forum statistics

Threads
1,136,345
Messages
5,675,220
Members
419,553
Latest member
hanahass

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