EXCEL 2007 - Combo Boxes

[vpiete]

I have an inventory database with a lot of blank spaces but these are not important, the first columns worksheet PDL-stock has the following columns, Stock Code, Description, Product Reference, Brand, UOM (unit of measure) Trade Price, Qty, Stock Value (Trade Price x Qty).

I wish to create a form where values from my database can be autofilled based on one of the columns, whats the best option?

 

[GlennUK]

What kind of Form? A worksheet formatted to act as a form? Or a Userform?

How do you want the autofilling of the combobox to work? Do you want a simply reflection of the contents of the database column? Or is it to be just the unique values from that column? And should the contents of the combobox be sorted in any particular way?

 

[vpiete]

Good morning and thank you GlenUK,
Worksheet 1 "PDL-stock" contains the inventory list as mentioned, Worksheet 2 "Stockcards" I have created templates for stock cards which I want to place into the storage areas with the stock items. My staff will use the cards when removing or returning stock to the store.

PDL-stock row 1 contains the column headers, rows 2 -125 currently hold the inventory list which will grow eventually.

On the Stockcards worksheet I designed my stock cards, I have 6 stock cards on the worksheet. Can I create a combo box to select the cells PDL-stock' A2, B2, C2 and G2 and autofil into the first template in Stockcards' B3 and D3.

To make this a real challenge I wish to combine PDL-stock' A2, B2 and C2 into Stockcard' B3

My next combo box will take data from PDL-stock' A3, B3, C3 and G3 to autofil Stockcard' B27 and D27 as above (my second template).

Template 3: PDL-stock' A4, B4, C4 and G4 to Stcokcard' B51 and D51
Template 4: PDL-stock' A5, B5, C5 and G5 to Stcokcard' B75 and D75
Template 5: PDL-stock' A6, B6, C6 and G6 to Stcokcard' B99 and D99
Template 6: PDL-stock' A6, B6, C6 and G6 to Stcokcard' B123 and D123

big Eeeeek,. sorry if this looks messy and would welcome any advice.

 

[GlennUK]

Is that you are creating a stockcard to match each row in the inventory?

Can you explain what the combobox purpose is exactly?


Does anyone else here have a problem understanding this ... it doesn't make much sense to me!!!

 

[vpiete]

I have over 5,000 stock items in many stores over our site, I dont want to create that many stock cards. I have managed to fit 6 stockcard templates onto a printable A4 page, if there is control or code that would fill these 6 templates, which, once filled I can print, then fill the templates with the next six inventory items.

A combo box may not be the best answer.

Yesterday I combined the contents of cells A2, B2 and C2 using the formula =A2&" " &" "&B2&" "&C2, the result looks like this;
23310320 Appliance inlet 3 pin 10A 250V 56AI310 Grey

 

[vpiete]

A B C G
1 Stock Code Description Product Qty
2 23310320 Appliance inlet 3 pin 10A 250V 56AI310 Grey 1
3 23610280 Socket cover 10A 56SOC10 PDL 56SOCVR10 3
4 23110020 Socket outlet 3 pin 10A 250V 56SO310 6
5 23110070 Socket outlet 3 pin 15A 250V 56SO315 8
6 23110100 Socket outlet 3 pin 32A 250V 56SO332 2




Electrical Stock Card
Description:23310320 Appliance inlet 3 pin 10A 250V 56AI310 Grey
Location:BIN060601 Qty: 1
Denom: EA
ROL: 0

DATE NAME ISSUE BAL.
30/05/11 | PVDB | 1 0.
__________|___________________________|______________|______
__________|___________________________|______________|______
__________|___________________________|______________|______
* ROL (Re-order level)

 

[vpiete]

I have a result which works, thank you to everyone who may have been trying to nut this one out.

I used Data Validation to import the Stockcode into the first template and then VLOOKUP to import the other data into the correct spaces on my Stock Card. On my second template card I created a pivot table based on the Data Validation selected at step 1, and VLOOKUP to autofill all templates.

I now have 24 templates which happily autofil on selecting from my drop down box

I know, still confusing but it works brilliantly.

 

[GlennUK]

Yeah still confusing ... but if it works brilliantly then GREAT!!! Thanks for letting us all know your progress, and well done.