# Fill series

#### JoshuaMars

##### New Member
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
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

##### MrExcel MVP
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

Your formula seems like it might work.

##### MrExcel MVP
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()

Replies
3
Views
125
Replies
2
Views
263
Replies
3
Views
130
Replies
4
Views
118
Replies
0
Views
65

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.

### Which adblocker are you using?

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

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