If I did go with running a macro, would I have to include all formating i.e.,
graphics, tables, etc., in the macro? Or would I first format the 2nd sheet
the way I need it and then the macro would populate the necessary cells thus
leaving the sheet2 format in tact?

Sorry for all the questions...as you can tell, this is a bit beyond my Excel
abilities.

"Tom Ogilvy" wrote:

Do you want this to generate lines everytime you make an entry or do you
want to make all your entries, then run a macro that generates the 2nd
sheet?

--
Regards,
Tom Ogilvy

"GreenMonster" wrote in message
...
Getting closer....I think.

You stated that "Whenever you enter an item in Col C it will trigger this
event" based on the code you wrote. However Col C will only change when
the
count changes. And when it does change it will change in increments of
"one"
ex (A2001-1, A2001-2, A2001-3) That's the easy part to figure out for me.

Lets pretend that Col C doesn't exist (in reality it doesn't) and we're
working with just Col A (Qty) and Col B (Dimension) Sheet2 should
populate a
specific number of line items that directly corresponds with the number
entered in ColA (Qty) of sheet1.

Also, I cut and past your code into sheet one and recieved the following
error in the debugger when attempting to enter data as you instructed "

'Send data to Job List
For i = 1 To lQty
Set Dest = Sheets("Sheet2").Cells(Rows.Count,

This happend whenever I entered a Qty (which will always be the first step
for me, the second step will be entereing the diminision, I'll never enter
data in A3(item).

"gocush" wrote:

The following should do what you have described ( with the following
modifications)

1. Enter data in Sheet1, which has

A B C
Qty Dimension Item
5 7x7 A201
2 4x4 A444
3 2x8 T343
4 5x8 A201
1 1x6 B222
4 3x9 T343

Sheet2 should look like this:

A B C D E

Job Unit Item Dim Completed

Then the following code goes in the Worksheet module for Sheet1.
Copy it
Activate Sheet1
Rt-click the sheet1 TAB
Click View code
Paste the code in the right panel

Whenever you enter an item in Col C it will trigger this event.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lQty As Integer
Dim sDim As String
Dim i As Integer
Dim Dest As Range
Dim Jobs As Range
Dim oCell As Range

On Error GoTo ErrHandler
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
'Adjust the following range as needed
If Not Intersect(Target, Range("C2:C500")) Is Nothing Then
If Target.Offset(0, -2) < 1 Then
Target.ClearContents
Target.Offset(0, -2).Select
MsgBox "Please enter a Quantity."

Application.EnableEvents = True
Exit Sub
End If
If Target.Offset(0, -1) = "" Then
MsgBox "Need a Dimension."
Target.Offset(0, -1).Select
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
'Get the Qty and Dim
lQty = Target.Offset(0, -2)
sDim = Target.Offset(0, -1)

'Send data to Job List
For i = 1 To lQty
Set Dest = Sheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)
Target.Copy Dest
Dest.Offset(0, 3) = sDim
Dest.Offset(0, 2).FormulaR1C1 = "=RC[-2]&""-""&RC[-1]"
Next

'Sort the Job List

Dest.CurrentRegion.Sort Key1:=Dest, Order1:=xlAscending,
Key2:=Dest.Offset(0, 1) _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

'Add the Unit #
Set Jobs = Sheets("Sheet2").Range("A2:A" & Dest.Row)
For Each oCell In Jobs
If oCell = oCell.Offset(-1, 0) Then
oCell.Offset(0, 1) = oCell.Offset(-1, 1) + 1
Else
oCell.Offset(0, 1) = 1
End If
Next oCell
End If
Application.EnableEvents = True
Exit Sub

ErrHandler:
Application.EnableEvents = True
End Sub




"GreenMonster" wrote:

thanks for your response - the prefix of the item number is a given
(job
number) the extension at the end is a count number based on a series
from the
number of units (i.e., unit XXX-01, is just the first item, -02 would
be the
next in the series, and so on)...

the job number is assigned at the beginning and can be located in the
data
file and the item number will drive from that based on the number of
units.
if twelve units, there will be xxx-01 through xxx-12.

Does that help explain it a little better?

"gocush" wrote:

Repeating the Item 5 times or 2 times etc is the easy part. But
knowing
which Item # would be a problem. I assume that sometimes the Item #
could be
other than A201-xxx. How would we know if it were, say B201-x, or
A301-x, or
A305-9 through A305-14 ?

Can you add to your explanation ?

"GreenMonster" wrote:

hmmm...I was afraid I didn't explain myself well enough. See if
this makes
better sense.

I am trying to create an order entry workbook that will take input
from the
order entry sheet and not only populate but create number of rows
appropriate
to the number of items in the data entry sheet.

I have, for instance, the following data:

Quantity Dimensions Item #
5 7 x 7 A201-1 through A201-5
1 8 x 4 A201-6
2 9 x 3 A201-7 through A201-8

And so on.

I am trying to take this input data and populate other related
tabs but not
with quantities, with individual items. For example,

Item # Dimensions Completed
A201-1 7 x 7 (left blank)
A201-2 7 x 7 (left blank)
A201-3 7 x 7 (left blank)
A201-4 7 x 7 (left blank)
A201-5 7 x 7 (left blank)
A201-6 8 x 4 (left blank)
A201-7 9 x 3 (left blank)
A201-8 9 x 3 (left blank)

The application is for manufacturing purposes for a schedule of
manufacturing tasks. I do not want it to be lumped into qty 5 or
qty 2 as
this application is for a checklist of activities for factory
workers that
they check off each individual item completed.

I am feeding several different spreadsheets with the workbook from
this same
data source, so you can tell this is very manual intensive.
Please help me
come up with the missing formula or macro or ..

Thanks.



"Tom Ogilvy" wrote:

Prefill the Pull sheet

=if('Data input'!A1="","",'Data input'!A1)

then drag fill down and across for as many cells as you might
need.

--
Regards,
Tom Ogilvy

"GreenMonster" wrote in
message
...
I'v built a spreadsheet that has multiple worksheets all of
which retrieve
data from a central worksheet (we'll call this central sheet
"data input")

The "data input" sheet is where the "quantity" of different
items are
added
to the workbook. The worksheet that pulls data (we'll call
this sheet
"data
pull") from the "data input" worksheet needs to identify the
quantity of
items in the "data input" worksheet, then populate the "data
pull"
worksheet
with that same number of rows.

In other words if Dat Entry has four rows of data, Data Pull
will see the
item quantity as four and return four rows of data.

If you change the number of rows in Data Pull to 7, then Data
Pull will
now
return 7 rows of data.

I hope this makes sense....if not email me at

and I'll try and explain further.

Thanks