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

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

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

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

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

=count should probably allow you to easy to determine thesize of the
array that you are looking for.

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

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

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

Either way would work.

The 2nd sheet will be printed and given to my workers once the data for
sheet1 is filled in. I guess which ever would be easiest to code, the macro,
or generating lines everytime an entry is made, and which one would make the
most sense, would be fine.

"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

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

As for the error, the following is all ONE LINE OF CODE:

Set Dest = Sheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)

It only shows up in this forum as 2 lines. The code works for me.

Now, when you say that Col C does not exist, you need to be specific with
your information. Do you want the Item# to show up on Sheet2 ? If you do,
how does the program know which Job number you are referring to ?

Notice that in my example in Sheet1 I did not enter under Col C a Unit
number (-1, -2 ....). This part was generated automatically by the code,
when moving data to Sheet2

If you do not want an Item # in Sheet2 this is easy. Let me know

The method that I used performs the data transfer each time you enter a new
line of data. It can be modified to run with a command button after entering
several new lines. This code would then be moved to a standard module rather
than a Sheet object module. Which do you want?

"GreenMonster" wrote:

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

Assuming column C doesn't exist, then where do we get the job number?

Anyway, this will ask you for it.

Sub Makesheet2()
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim i As Long, k As Long
Dim rw As Long
Set sh = ActiveSheet
Set sh1 = Worksheets.Add(After:=sh)
ans = InputBox("what is the Job Number")
If Trim(ans) = "" Then Exit Sub
i = 0
rw = 1
sh1.Range("A1:C1").Value = Array( _
"Item #", "Dimensions", "Completed")
Set rng = sh.Range(sh.Cells(2, 1), _
sh.Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
For k = 1 To cell.Value
i = i + 1
rw = rw + 1
sh1.Cells(rw, 1) = ans & "-" & Format(i, "00")
sh1.Cells(rw, 2) = cell.Offset(0, 1)
Next
Next
sh1.Columns(1).Resize(, 3).AutoFit
End Sub

With this starting in A1 of the active sheet and answering A201 to the input
box,

Quantity Dimensions
5 7 x 7
2 5 x 8
3 1 x 5

it added a new sheet and produced:
Item # Dimensions Completed
A201-01 7 x 7
A201-02 7 x 7
A201-03 7 x 7
A201-04 7 x 7
A201-05 7 x 7
A201-06 5 x 8
A201-07 5 x 8
A201-08 1 x 5
A201-09 1 x 5
A201-10 1 x 5

--
Regards,
Tom Ogilvy


"GreenMonster" wrote in message
...
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

I'm afraid I'm confusing the matter more then I should. I'm still unable to
get the code to function without getting the error. I'm sure I'm doing
something wrong as I'm not at all proficient in writing this type of code in
excel. Would it be possible for you to email me your spreadsheet that is
working so I can compare it to the one I'm using and recieving errors with?

Also, I'd be more then happy to email you the actual spreadsheet I'm
building so you could see exactly what it is I'm trying to do. If that would
help.

Thanks so much for the help thus far...I really appreciate your time. my
email address is

"gocush" wrote:

As for the error, the following is all ONE LINE OF CODE:

Set Dest = Sheets("Sheet2").Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)

It only shows up in this forum as 2 lines. The code works for me.

Now, when you say that Col C does not exist, you need to be specific with
your information. Do you want the Item# to show up on Sheet2 ? If you do,
how does the program know which Job number you are referring to ?

Notice that in my example in Sheet1 I did not enter under Col C a Unit
number (-1, -2 ....). This part was generated automatically by the code,
when moving data to Sheet2

If you do not want an Item # in Sheet2 this is easy. Let me know

The method that I used performs the data transfer each time you enter a new
line of data. It can be modified to run with a command button after entering
several new lines. This code would then be moved to a standard module rather
than a Sheet object module. Which do you want?

"GreenMonster" wrote:

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