Creating Custom Internal Product Order Form using a Drop List

[spoQn]

Greetings all,

this is my first visit to MrExcel. The reason being i know next to nothing about excel but find myself in a position where i need to acquire some knowledge to increase my productivity @ work.

the situation is thus:

When ordering products from a regular supplier, we would copy/paste or just type and item description onto a blank 'form' template, then either copy/paste the corrisponding item code, or go look it up in the price book then manually enter it.

i've managed to take all the previous order data from the last 3 years (100+ seperate order forms)

i've gone through and deleted all the duplicate entries, and compiled it into one long list comprised of 2 columns.

Column A contains the Product Codes e.g. "W30752" or "001697"
Column B contains the product Name e.g. "40mm Pipe" or "Cleaner 2.5kg"

what i want to do, but can't seem to figure out how, is to have the blank order form with 3 columns, 'Product Code' Product Name' and 'Qty"
in the 'Product Name' Column, in each row, have a drop down list the links to the list of products on another sheet.

I then want to be able to scroll through the list and select a product and have the product code appear next to the name in column A. i will then manually enter the qty required.

any help would be appreciated, i think i need to use either vlookups or some form of data validation but i'm not sure.

thanks

 

[Peter_SSs]

spoQn

Welcome to the MrExcel board!

Try the steps below. It is set up so that if you add more products to your main list later, they will be included in your drop-down list and lookup table. My main list is on a sheet called 'Master List'. You would need to change references to that in steps 2 & 3 below to match your sheet name. My blank order sheet is called 'Order Form' but that is not relevant to the solution.

On the 'Master List' sheet:

1. Set it up like below. I assume you already have column A formatted as Text so the leading zeros don't drop off codes like '001697'. Naturally you will have a much longer list than me.

Excel Workbook
	A	B	C	D	E
1	Code	Name		Count:	2
2	W30752	40mm Pipe
3	001697	Cleaner 2.5kg
4
Master List

2. Insert|Name|Define... in the 'Names in workbook:' box type 'Codes' (without the ' ')
In the 'Refers to:' box put
=OFFSET('Master List'!$A$1,1,0,'Master List'!$E$1)
Press OK

3. Repeat step 2 with name 'ProdTable' and Refers to:
=OFFSET('Master List'!$A$1,1,0,'Master List'!$E$1,2)


Now on the 'Order Form' sheet:

4. Select A2:Axx where xx is the max row you might want to go to and apply the Data Validation shown (Access through data|Validation...)

5. Select B2 and enter the formula shown. Copy this formula down to row xx (same row as step 4.

Excel Workbook
	A	B	C
1	Code	Name	Qty
2	001697	Cleaner 2.5kg
3	W30752	40mm Pipe
4	W30752	40mm Pipe
5
Order Form

#VALUE!

 

[spoQn]

Thank you so much for your help Peter.
I am having some difficulties however, with steps 4 and 5 and I have a question regarding steps 2 and 3.

with steps 2 & 3:

2. Insert|Name|Define... in the 'Names in workbook:' box type 'Codes' (without the ' ')
In the 'Refers to:' box put
=OFFSET('Master List'!$A$1,1,0,'Master List'!$E$1)
Press OK

3. Repeat step 2 with name 'ProdTable' and Refers to:
=OFFSET('Master List'!$A$1,1,0,'Master List'!$E$1,2)

am i to assume this means, for step 2 select cell A1 and follow the instructions and for step 3, select cell B1 and Repeat?


as far as steps 4 & 5 are concerned my problems are as follows:

4. Select A2:Axx where xx is the max row you might want to go to and apply the Data Validation shown (Access through data|Validation...)

is this in reference to this box?

<table style="border-style: groove; border-color: rgb(0, 0, 255); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Data Validation in Spreadsheet</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Allow</td><td>Datas</td><td>Input 1</td><td>Input 2</td></tr><tr><td>A2</td><td>List</td><td>
</td><td>=Codes</td><td>
</td></tr></tbody></table></td></tr></tbody></table>
if so, i'm not sure i understand how to do this. below is a snapshot of my workbook:

i can't see where to enter the above data into the data validation window.

also as you can see from above, the cells for the product code and product name on the form consist of 2 rows each, though they are cell merged, will this be a problem, if so how do i over come it?

5. Select B2 and enter the formula shown. Copy this formula down to row xx (same row as step 4.

im also unsure how to copy the formula down the entire column. i know there is a keyboard shortcut but i cannot recall what it is, or is it simply a case of selecting the cell then copy pasting it to the rows below it.
i also have the same problem as stated above with this column in that each 'entry' is actually 2 merged rows, how will this effect the solution if at all?


also i'm not sure if i understand the above instructions correctly, but i think if followed, i will get a drop down list in the PRODUCT CODE column, not in the PRODUCT NAME column as i require, is this accurate or am i incorrect?




thank you again for you time

kind regards
spoQn

 

[Peter_SSs]

OK, we have a lot to cover here.

A. My recommendation is to avoid merged cells whenever possible. Not sure if you can, but from what I can see, it looks like you could just make col A wider and just use that for 'Part No' then make col B even wider and use for 'Description', similar for col C for 'Quantity' and increase your row height. See here for considerable discussion about mergerd cells.
http://www.mrexcel.com/forum/showthread.php?t=335124

However, I don't think meged cells will stop you doing what you want and I have tried below to give instructions for the merged setup you have.

B. Looks like your main list is on a sheet called 'ProductList' and your order form is 'Orderform'. I have adjusted my instructions/formulas for those names.

C. I will go over the steps again below, but in answer to your questions about steps 2 & 3: It doesn't matter what cell(s) you select before performing the steps, you just type or copy/paste what I suggest into the appropriate boxes.

D. I did get it wrong about what you wanted to appear in the drop-down, corrected below.

Here are the (revised) steps again:

On the 'ProductList' sheet:

1. Set it up like below. I assume you already have column A formatted as Text so the leading zeros don't drop off codes like '001697'. Naturally you will have a much longer list than me.

Excel Workbook
	A	B	C	D	E
1	Code	Name		Count:	2
2	W30752	40mm Pipe
3	001697	Cleaner 2.5kg
4
ProductList

2. (Remember my point C above) Insert|Name|Define... in the 'Names in workbook:' box type 'Names' (without the ' ')
In the 'Refers to:' box put
=OFFSET(ProductList!$B$1,1,0,ProductList!$E$1)
Press OK

3. This step is no loger required.

Now on the 'Ordeform' sheet:

4. Select C12 (this will actually select C12:G13 as they are merged) then
Data|Validation...|Settings tab|Allow: List|Source: =Names|OK

5. Select B12 (this will actually select B12:C13 as they are merged) then enter this formula:
=IF(C12="","",INDEX(ProductList!A:A,MATCH(C12,ProductList!B:B,0)))

6. Select B12:G13 (that is, two groups of merged cells containing your formula and Data Validation. Click and drag the Fill Handle (the little black square at the bottom right of the selection) down as many rows as you want but I suggest not too many until you are sure we have this working. You can drag down further later. If you haven't already merged cells further down the sheet, this dragging down will do it for you.

Here's a shot of my sheet with the Data Validation, formula and a few rows completed.

Excel Workbook
	A	B	C	D	E	F	G	H	I
10
11	Part No		Description					Quantity
12	001697		Cleaner 2.5kg					50
13
14	W30752		40mm Pipe					1,000
15
16	W30752		40mm Pipe					28
17
18
19
Orderform

#VALUE!

 

[spoQn]

Amazing,
Absolutely Amazing.

Thank you so so much for your help Peter, you are obviously an expert in your field, which is no doubt due to many years of hard work and dedication.

Everything works perfectly, well as far as i can tell so far :P

the only hurdle i had was with step 6

6. Select B12:G13 (that is, two groups of merged cells containing your formula and Data Validation. Click and drag the Fill Handle (the little black square at the bottom right of the selection) down as many rows as you want but I suggest not too many until you are sure we have this working. You can drag down further later. If you haven't already merged cells further down the sheet, this dragging down will do it for you.

If i selected both groups, that is the "Code" group as well as the "name" group, and tried to drag them down together, i got an error

"This operation requires the merged cells to be identically sized"

i overcame this simply by dragging down each group separately.

now all i have to do is stream line the Product Names so the list makes sense and we are in business!

again, thank you so much for your help Peter,

Kind Regards
spoQn