Autofil Solutions?

**blincan** · 05-02-2012, 02:19 PM

I giant bill of ladings worksheet in excel that has a list of the item numbers and they are acending and it looks something like this :

0 0 0 0 2 9 1 0 0
0 0 0 0 2 9 1 0 1
0 0 0 0 2 9 1 0 2
0 0 0 0 2 9 1 0 3
0 0 0 0 2 9 1 0 4
0 0 0 0 2 9 1 0 5
0 0 0 0 2 9 1 0 6
0 0 0 0 2 9 1 0 7
0 0 0 0 2 9 1 0 8
0 0 0 0 2 9 1 0 9
0 0 0 0 2 9 1 1 0
0 0 0 0 2 9 1 1 1
0 0 0 0 2 9 1 1 2
0 0 0 0 2 9 1 1 3
0 0 0 0 2 9 1 1 4

I need this to fill an entire sheet of data upwards of 100+ rows.
My problem is that I feel like you can set a constraint on the Last two columns(to autofil) and have the last column acending by 0-9 and everytime that goes through that entire sequence then the 2nd to last column changes to the next 0-9. I was thinking a for loop in VBA, but I wasn't sure about the functionality of Excel in Autofil regaurds.

Any insight would help lots. Thanks!

**DGagnon** · 05-02-2012, 02:40 PM

you can fill in the first row manualy, then in I2 use this

=IF(I1=9,0,I1+1)

and in H2, filling back to A2 use this
=IF(SUM(I2:$I2)=0,IF(H1=9,0,H1+1),H1)

then fill down

see attachement