Excel Order Form based on Price List form

[hmessing]

I hope all of this will make sense. I have an Excel spreadsheet with 2 worksheets. One is a price list with approximately 150 or so line items. The other is the order form. Right now I have the order form set up with drop downs for a customer to pick the part number to order. However, they might not be familiar with the parts and it would make it difficult to find the right part number unless they write it down. This will work fine if it is the only way to do it.

I am trying to add a column to the price list itself a quantity to order that will automatically update the order form with the parts and quantities that a customer wants to order. There could be several line items that a customer wants to order.

How/can I do this? My Excel skills are limited so it needs to be something simple if at all possible.

Thanks!

 

[SkipVought]

Hi,

Use INDEX and MATCH to return the Unit Cost, for a given part Number. I use Named Ranges for clarity...
[tt]
=INDEX(UnitCost,MATCH(OrderedPart,PartNumber,0),1)
[tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hmessing]

I'm sorry, I'm not understanding what you are saying. Here's a screenshot

https://weathermatic.box.net/shared/static/g192nv26pn.jpg

that might help better explain what I am trying to do.

I want the order form to automatically pull only the parts that have a quantity entered.

Maybe what you're suggesting will do this but I'm not understanding how to implement it.

Thanks for your help.

 

[SkipVought]

I have looked at your example.

1. Why do you have Qty in the Part Worksheet, which I assume is the part list of 150 items, unless it is an On Hand Balance?

2. Lets assume that the Order Form headings are in row 1 and the items are in column A.

Why are you merging columns A & B? Although it is possible, it is not the best thing to do, as there are all sotrs of problems and extra work that you will encounter with merging.

Column C is your Price.
[tt]
C2: =INDEX(Price,MATCH(A2,Part,0),1)
[/tt]
Using Named Ranges on your part list. faq68-1331.

Just a suggestion: I'd use Data > Validation -- LIST rather than inserting all those drop down controls. You get the same effect without the maintenance.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]