Auto fill from data base drop down list

**ciapul12** · 09-03-2011, 06:41 AM

Hi Does anyone know how to create a data base where products has got their own specifications so if I choose product A from drop down list it will auto fill cells with product specifications below I have been trying to resolve this issue for a while now and I think I need to use VBA to get it right.
Thanks Dan

**teylyn** · 09-03-2011, 06:57 AM

Hello Dan,

this can be done with lookup formulas, for example Vlookup or Index/Match.

You will need a reference table that lists the product specifications for each product. The lookup formula will need to refer to that table.

Attached, please find an example with two sheets. The Product sheet lists products and their properties. In the "Pick and choose" sheet, you can select a product and the properties will be populated by an Index/Match formula.

Depending on how your data is laid out, a suitable solution for your situation may differ.

If you need help with your specific data layout, you need to upload a file that represents that.

cheers,

**ciapul12** · 09-03-2011, 07:34 AM

Thanks a lot this is what I was looking for....

**ciapul12** · 09-03-2011, 08:30 AM

Hi Looks like I need more help from you as I am the beginner in excel compare to other users and your self...

I have attached an example of what I trying to achieve.

PLEASE HELP ME!!

**teylyn** · 09-03-2011, 08:45 AM

Hello,

I needed to take a few guesses, since the "prod specifications" sheet does not have any column headings to identify what column has what value.

I created a data validation for the layout sheet, cell B4, with the list of products in column A of the "prod specifications" sheet.

Then I used a VLookup formula to find the contents of the product that matches the value in B4 of the layout sheet:

=VLOOKUP($B$4,'prod specifications'!$A$3:$Q$8,COLUMN(B1),FALSE)

copied across.

The parameter COLUMN(B1) will change to C1, D1, E1, etc, when copied across, to return the number 2, 3, 4 etc. to specify the column number of the lookup table to return for a match.

Read up about Vlookup in the Excel help to understand how it works.

cheers,