Help with Autofill Pattern: S/N: XQ3017-1, XQ3018-1, XQ3019-1

I need some help formatting the above mentioned pattern. I figured out how to do this over a year ago, but like the dummy I am forgot to save the instructions!

I seem to recall having to enter a formula, but I'm not sure. This is the formatting that I need:

FLAIR
MODEL SN-580-24-AB
Serial Number: XQ3017-1

FLAIR
MODEL SN-580-24-AB
Serial Number: XQ3018-1

FLAIR
MODEL SN-580-24-AB
Serial Number: XQ3019-1

FLAIR
MODEL SN-580-24-AB
Serial Number: XQ3020-1

FLAIR
MODEL SN-580-24-AB
Serial Number: XQ3021-1

Each line is in a separate cell. If anyone knows how to use autofill to continue this sequence of numbers, I'll be eternally grateful!

Thank you

If you put this in the cell where you want the XQ3017-1 number, it can be copied down to other cells where you need it:

Formula:

Please Login or Register  to view this content.

Amend the +3016 as required to get the starting number you need - just enter one number below the number you want.


However, getting it to auto-fill, with the blanks, is trickier. If there's nothing else in the column and 'Serial Number:' is in column A, then this should work:

Formula:

Please Login or Register  to view this content.

If you need the column to fill with a blank, then the Model, then the Serial Number, then you'll need to do this (I'm going to assume your data is in columns A and B, starting on row 2 after headings):
1 - put the Model in B3
2 - put the first formula above in B4
3 - select B2 to B4, then drag down to auto-fill
Excel should then autofill: blank / model / incrementing serial number / blank / model / incrementing serial number / etc.
Whenever the Model changes, you'll need to re-do this.


I hope that helps (and that it makes sense! ).