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

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

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