Formula to Count Up to a Specific Value as Repeating Number

[rylock]

I'm trying to send out lists to workers that gives them approximately 20 people each. I've already got the data, but ultimately I'd like to turn this into a PDF for each worker (which I would normally use subtotals/page breaks for) -- but I can't because there's no recurring information that would allow subtotals to work properly.

So, basically, if I wanted Excel to count up to 20 but using '1's every time, then when it hits 20, switch to '2's, etc. -- is that possible? This would allow me to use the subtotals function to break the pages properly.

Thank you!

[pjwhitfield]

Assuming you want this value in column A

Please Login or Register  to view this content.

[FDibbins]

No need for VBA for this. Put this in the cell where you want the numbering to start, and copy down...
=INT(ROW(A1)/20)+1

(not sure how this will be affected by subtotals, though)

[rylock]

No need for VBA for this. Put this in the cell where you want the numbering to start, and copy down...
=INT(ROW(A1)/20)+1

(not sure how this will be affected by subtotals, though)

Thanks! Works great. If subtotals affect it, I'll just copy and paste with values.

[FDibbins]

Glad it worked fror you and thanks for the feedback

[rylock]

Actually -- it's so close to perfect I almost missed a quick flaw. When I do this, the first grouping of '1's only count up to 19 for some reason -- which throws off the rest (that correctly count to 20).

Any idea why? I'm using the formula in Cell M2 and the formula exactly how you wrote it above. If I change ROW(A1) to ROW(A2) for example, I've noticed that the first grouping of '1's go down to a count of 18 -- if you change it to ROW(A3), it goes down to a count of 17, etc. But the remainder of the list stays correct (all other numbers are groupings of 20).

Know what I can do about this? It's throwing everything off by one.

[canapone]

Hi All,

a small adjustment could be

Please Login or Register  to view this content.

Cheers

[pjwhitfield]

No need for VBA for this. Put this in the cell where you want the numbering to start, and copy down...
=INT(ROW(A1)/20)+1

(not sure how this will be affected by subtotals, though)

sometimes I think I use a sledgehammer to crack a nut! Just shows theres usually an easy answer

[FDibbins]

canapone, thanks for the assist, nicely done

[martindwilson]

never know why int() is used there is a very useful function called ceiling()
=CEILING(ROW(A1)/20,1)