Order form design

[Learner101b]

I am a first time user of this discussion group, semi-technical and use Excel
2003.

I want to create an order form on worksheet 1 that would also create a
specific vendor purchase order on a separate worksheet. In other words, I
want worksheet 1 to be an alphabetical list of many items that would be
purchased from 10 different vendors. When a customer puts a number in the
'quantity' column, I want that line including item number, description, etc.
to move to another worksheet that is specific to the vendor the item is
ordered from.

I have 2 requirements that are giving me problems in developing a design.
(1) I prefer to make additions/deletions from the main list on worksheet 1 to
make updating easy rather than having to go to the vendor purchase order
worksheet and make changes there as well. (2) Some vendors will have 30
items on the main list (which will be multiple pages), but most customers
will only order a couple of them at a time. I would like to keep the final
vendor purchase order to a single page so I prefer to not have all 30 items
listed on the purchase order with only a couple of the items having
quantities in the 'order' column.

I would really like to 'push' the information from the order form on
worksheet 1 to the vendor purchase orders when there is a quantity in the
order column, but I do not think Excel can do this.

I am pretty good with Excel basics, but have never filtered lists, used
complex conditional formulas or functions, used pivot tables, etc. But I am
willing to learn.

Thanks for any help.

 

[Max]

Here's a formulas play which delivers what you're after

Illustrated in this sample:
http://www.savefile.com/files/1267592
Auto-Ordering to Diff Vendors.xls

In a sheet: Order,
Source data is assumed in cols A to D: Item#, Desc, Vendor, Qty
with data from row2 down, eg:

Item# Desc Vendor Qty
Item1 Desc1 Vend1 5
Item2 Desc2 Vend1
Item3 Desc3 Vend2 2
etc

List the vendors in G1 across
The vendor listing must match what's indicated within col C, viz.: Vend1,
Vend2, ...

Put in G2: =IF($D2="","",IF($C2=G$1,ROW(),""))
Copy G2 across as far as required,
fill down to cover the max expected extent of source data in cols C and D

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

Then in a sheet named as: Vend1 (this will be the purchase order sheet for
Vend1)
with the same col labels placed in say, B2:E2 viz: Item#, Desc, Vendor, Qty

Put in B3
=IF(ISERROR(SMALL(OFFSET(Order!$F:$F,,MATCH(WSN,Order!$G$1:$IV$1,0)),ROWS($1:1))),"",INDEX(Order!A:A,MATCH(SMALL(OFFSET(Order!$F:$F,,MATCH(WSN,Order!$G$1:$IV$1,0)),ROWS($1:1)),OFFSET(Order!$F:$F,,MATCH(WSN,Order!$G$1:$IV$1,0)),0)))

Copy B3 across to E3, fill down to say, E11, viz copy down by the smallest
possible range sufficient to cover the max expected number of order lines for
any vendor. Here, I've assumed that 9 lines/rows (ie rows 3 to 11) is
sufficient.

Cols B to E will return only the purchase order (PO) lines for the vendor:
Vend1 where Qty is not blank from the sheet: Order, with all lines neatly
bunched at the top.

Now, just make a copy of the sheet: Vend1, rename it as the next vendor:
Vend2, and you'd get the PO results for that vendor. Repeat the copy >
rename sheet process to get the rest of the 10 vendor sheets (a one-time
job). Adapt to suit ..

---

 

[Learner101b]

Wow, your answer is incredible and I can't tell you how much I appreciate it.
I have been wrestling with different ideas for weeks, but without result.

I should tell you your design is a little over my head, but your example
will help me understand and work through it. Thank you for that. I am the
type that will spend days learning how/why it works as I use your model to
create my own form. I never would have been able to do it on my own.

Thank you MAX for taking your time to help me out.

Learner101b

Here's a formulas play which delivers what you're after

Illustrated in this sample:
http://www.savefile.com/files/1267592
Auto-Ordering to Diff Vendors.xls

In a sheet: Order,
Source data is assumed in cols A to D: Item#, Desc, Vendor, Qty
with data from row2 down, eg:

Item# Desc Vendor Qty
Item1 Desc1 Vend1 5
Item2 Desc2 Vend1
Item3 Desc3 Vend2 2
etc

List the vendors in G1 across
The vendor listing must match what's indicated within col C, viz.: Vend1,
Vend2, ...

Put in G2: =IF($D2="","",IF($C2=G$1,ROW(),""))
Copy G2 across as far as required,
fill down to cover the max expected extent of source data in cols C and D

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

Then in a sheet named as: Vend1 (this will be the purchase order sheet for
Vend1)
with the same col labels placed in say, B2:E2 viz: Item#, Desc, Vendor, Qty

Put in B3:
=IF(ISERROR(SMALL(OFFSET(Order!$F:$F,,MATCH(WSN,Order!$G$1:$IV$1,0)),ROWS($1:1))),"",INDEX(Order!A:A,MATCH(SMALL(OFFSET(Order!$F:$F,,MATCH(WSN,Order!$G$1:$IV$1,0)),ROWS($1:1)),OFFSET(Order!$F:$F,,MATCH(WSN,Order!$G$1:$IV$1,0)),0)))

Copy B3 across to E3, fill down to say, E11, viz copy down by the smallest
possible range sufficient to cover the max expected number of order lines for
any vendor. Here, I've assumed that 9 lines/rows (ie rows 3 to 11) is
sufficient.

Cols B to E will return only the purchase order (PO) lines for the vendor:
Vend1 where Qty is not blank from the sheet: Order, with all lines neatly
bunched at the top.

Now, just make a copy of the sheet: Vend1, rename it as the next vendor:
Vend2, and you'd get the PO results for that vendor. Repeat the copy >
rename sheet process to get the rest of the 10 vendor sheets (a one-time
job). Adapt to suit ..

---

I am a first time user of this discussion group, semi-technical and use Excel
2003.

I want to create an order form on worksheet 1 that would also create a
specific vendor purchase order on a separate worksheet. In other words, I
want worksheet 1 to be an alphabetical list of many items that would be
purchased from 10 different vendors. When a customer puts a number in the
'quantity' column, I want that line including item number, description, etc.
to move to another worksheet that is specific to the vendor the item is
ordered from.

I have 2 requirements that are giving me problems in developing a design.
(1) I prefer to make additions/deletions from the main list on worksheet 1 to
make updating easy rather than having to go to the vendor purchase order
worksheet and make changes there as well. (2) Some vendors will have 30
items on the main list (which will be multiple pages), but most customers
will only order a couple of them at a time. I would like to keep the final
vendor purchase order to a single page so I prefer to not have all 30 items
listed on the purchase order with only a couple of the items having
quantities in the 'order' column.

I would really like to 'push' the information from the order form on
worksheet 1 to the vendor purchase orders when there is a quantity in the
order column, but I do not think Excel can do this.

I am pretty good with Excel basics, but have never filtered lists, used
complex conditional formulas or functions, used pivot tables, etc. But I am
willing to learn.

Thanks for any help.

 

[Max]

Welcome. The feedback is appreciated.

---