Serial Number Report Generation - multiple variables

[abublitz]

Hi all,

I've been given the task of 'bandaiding' this quality control sheet (normally these are created from an access database but our database cannot handle serial numbers made up of variables - thats an entirely different headache to deal with).

The people who will be using this spreadsheet are not avid excel users so I'm attempting to make it as 'easy' as possible for them.

Essentially, the users will enter the Model# (G2), Job# (G3), the starting Serial# (O2) and the Prefix (N2). How I have it set up now, the spreadsheet will populate the record with 50 serial numbers in 2 columns. 50 is the max that will fit on one sheet. If the job is put on for more than 50 serial numbers (generally is) the user will add additional sheets. Currently, I have 'PrevSheet' function set up, so all they need to do is duplicate Sheet2 and the serial numbers will continue in sequential order.

While not the greatest solution, this works well enough.

The issue I am running into is that not all models will have 50 serial numbers per sheet. Some models go by the number of devices that fit in a box. So say a job for ModelA is placed for 500, the devices are packaged in boxes of 25, so I would need to generate 20 quality control sheets with only 25 serial numbers per page.

Another situation is a lot of the time, jobs are put on according to order numbers, which may not be evenly dividable by 50. Say a job was put on for 620 devices, I would need 12 full sheets and then 1 sheet with only 20 serial numbers generated on it.

It's not difficult for me to just delete content/move the formulas around to suit what is needed but the people who should be printing these are not comfortable/that skilled in excel.

I'm probably not explaining this well enough. I've attached a sample. Does anyone have any ideas?

[avk]

In sheet2 "O2"

Please Login or Register  to view this content.