Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Order form design
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Order form design
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,Or der!$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,,MATC H(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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Learner101b" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Order form design
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 "Max" wrote: 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,Or der!$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,,MATC H(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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Learner101b" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Order form design
Welcome. The feedback is appreciated.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Learner101b" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to design a form | New Users to Excel | |||
Design a form | New Users to Excel | |||
Design complicated form | New Users to Excel | |||
Worksheet form design | Excel Discussion (Misc queries) | |||
design form? | New Users to Excel |