How to search and add a value from sheet1 to sheet2

[Guest]

Hi,

Let's say you have one sheet with a list of products.
Column1 = product name
Column2 = product number

I would like to have a cell on Sheet2, where I can start typing something,
and it would let me see a list based on what I typed so far.
E.g when I type "Volks" it should show a list of all Volkswagen Cars.

If I then choose the value, it should use this value and add the product
number in the cell next to it.

What function should I use, or how can I do this?

Thanks,

Roel

 

[Gord Dibben]

Roel

You could use Data Validation drop-down list for the selecting the product
name and VLOOKUP for adding the product number.

DV info at Debra Dalgleish's site.

http://www.contextures.on.ca/xlDataVal01.html

Also VLOOKUP info.

http://www.contextures.on.ca/xlFunctions02.html

As far as the "autocomplete" goes, a regular DV drop-down will not function
this way.

To get autocomplete use a combination of DV and a ComboBox.

Data Validation Combobox-- Double-click on a cell that contains a data
validation list, and a combobox appears -- font size can be set, more than 8
rows displayed, and autocomplete can be enabled. Macros must be enabled.
DataValCombobox.xls 32 kb

http://www.contextures.on.ca/excelfiles.html#DataVal


Gord Dibben Excel MVP