Incrementing Data based on user input

[Guest]

My basic goal is to be able to have a user input 3 pieces of information and
have Excel increment the data based on the information entered. More
specifically, the user will enter "Job Number", "Number of Lots", and "Number
of Parts in each Lot" as such:
Job Number: 12345-67-8910
Number of Lots: 3
Parts per Lot: 10

I would like for Excel to increment the data and print in a format that will
act as a "label". The data should be incremented as follows:

12345-67-8910 1 1
12345-67-8910 1 2
12345-67-8910 1 3 ...etc.

12345-67-8910 2 1
12345-67-8910 2 2
12345-67-8910 2 3 ...etc.

12345-67-8910 3 1
12345-67-8910 3 2 ...etc.

Spaces are not required between the job number, lot number, and part number,
i just added them for emphasis on what i would like to achieve.

I am guessing that the best way to achieve this is to use macros, but i am
not very "macro savvy".

Thanks in advance.

 

[vezerid]

A formula solution... (VBA is also easy)
Say Job Number in F1, No of Lots in F2, Parts per Lot in F3.
Your labels start in A1:Cn, where n=No of Parts*Parts per Lot.

In A1:
=$F$1

In B1:
=INT((ROW()-1)/$F$3)+1

In C1:
=MOD(ROW()-1,$F$3)+1

Does this help?
Kostis Vezerides

 

[Guest]

That actually does what i would like with the exception of stopping at the
specific number of lots. For instance, if i put in 3 lots with 10 parts per
lot, the counting continued on to the 4th lot, 5th, etc.

I was actually thinking something along the lines of having all of this in a
macro and having that macro load when the page loads. Along with that, i
would like the macro to disply an input box asking for job number, then one
for Lots of parts, then another one for parts per lot. Once the last input
box has been "OKed" i would like it to display all of the data so that i can
format that for a label printer.

I hope this is making sense.

 

[Guest]

OK. I have been toying around with these formulas and they are functioning
great!

I have one more request: I have recently been asked to add the feature to
start at a given "Lot" and count from there.

For instance, we might release a "Lot" of parts that starts with "Lot 12".
So i would need it to count as follows:

12345-67-8910 12 1
12345-67-8910 12 2
12345-67-8910 12 3
12345-67-8910 12 4 ...etc

Any thoughts?