Fill series

[JoshuaMars]

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.

 

[Rastaman]

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]

As an alternative, you can construct a formula that'll do the same thing...

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

 

[JoshuaMars]

PaddyD,

Your formula seems like it might work.

Could you explain your formula please.

Thanks in advance.

 

[PaddyD]

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()