Look up an item by starting to type see possible choices

[Guest]

I have a parts list with part names, descriptions and prices in Excel. I
would like to retrive prices and descriptions for an order by starting to
type the part name in a cell and have excell suggest possible parts based on
the first letters of the name so I could click on the one I want.

Thanks

 

[Gord Dibben]

I would suggest a DV dropdown List with a combobox assist.

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

Then VLOOKUP formulas to return the descriptions and prices.

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


Gord Dibben MS Excel MVP

 

[Guest]

Thank you. I have successfully implemented your suggestion on the parts list
and it is a huge improvement.

However I have to scroll through the drop down list to find the part ID I
need.

Is there a way I can get to the section of parts I am looking for in the
drop down list by typing the first letter or two of the ID I am looking for?

Thank you again.

Vince R

 

[Gord Dibben]

Vince

If you followed Debra's instructions on creating the DV list with ComboBox you
should have Autocomplete in the partname list.

Download her sample workbook from that site to see it in action.


Gord

 

[Guest]

Thanks again. I got it to work...very slick.

I would like to use this look up capability in a different worksheet (our
bidding program) I get an error message saying data validation will not work
with references from other workbooks or worksheets.

I can probably figure out a way to work around this but it will be messy.

Anyh suggestions?

Many thanks again

 

[Gord Dibben]

If you give the list range on the other sheet or workbook a defined name and use
that name in the DV source dialog you will be OK.

In source dialog enter =MyRange where MyRange is your defined name.

See Debra's site for more.

http://www.contextures.on.ca/xlDataVal01.html#Name for sheet in same workbook

http://www.contextures.on.ca/xlDataVal05.html for sheet in other workbook


Gord