Need to add serial number Suffix based on Quantity

[Juxtpos]

Hi,

This has got to be a simple solution.

I'm needing to add a serial number suffix that is based on a quantity.

If I have a prefix in B1 and my QTY in B2, I need to automatically generate serial number suffix, i.e -001 through a max of 1000 in column D.

Is there an easy way to do this? I would like to just be able to put the job specific data in one area and have excel do the rest.

Thanks!

 

[Rick Rothstein]

Put this formula in cell D1 and copy it down to D1000...

=IF(ROW(D1)<=B$2,TEXT(ROWS(D$1:D1),""""&B$1&"""-000"),"")

 

[Joe4]

Put this in D1 and copy down:
=IF(ROW()<=$B$2,$B$1&"-"&TEXT(ROW(),"000"),"")

 

[Juxtpos]

Thanks!
In cases where I need to start the SN at 150, and added a SN start cell in A3 as shown above so that the output would read
45678-150
45678-151
45678-152 and so on.

How would that be done?

 

[Joe4]

=IF(ROW()<=$B$2,$B$1&"-"&TEXT(ROW()+147,"000"),"")

 

[Rick Rothstein]

Here is how my formula would be changed (the number in red is the starting number, 150 for your present case)...

=IF(ROWS($1:1)<=B$2,TEXT(ROWS($1:150),""""&B$1&"""-000"),"")

 

[Juxtpos]

Those work, but I'm trying to keep the end user from manipulating the formula.

Is there a way to make it work off of the cell in B3 as the specified starting point? This is all on a protected sheet where they specify the basic data on sheet 1 and the needed data populates on sheet 2.

 

[Rick Rothstein]

For the layout you posted in Message #7, put this formula in your starting cell (C1 for your posted layout but it would work for any starting cell) and copy down...

=IF(ROWS($1:1)<=B$2,TEXT(ROWS($C1:INDEX(C:C,B$3)),""""&B$1&"""-000"),"")

 

[Juxtpos]

That works but with the opposite effect. It counts down as opposed to up. So the output reads 99999-147, 99999-146, 99999-145....It should be ascending based on the number in B3, not descending. It only counts up if I start at 1. If I have a qty of 25 and start at 5, it counts down from 5 to 1, then back up again.

 

[Joe4]

Try this:
=IF(ROW()<=$B$2,$B$1&"-"&TEXT(ROW()+$B$3-1,"000"),"")