How to restrict input to a list of values - with a twist

R

Rod

What I need to do can ALMOST be done with a drop-down list.

My situation is this. I have a list of approximately 500 items in a table.
Next to the item name is a unit cost. So we have a table with 2 columns (A,B)
which are "Item" and "Cost" respectively. This table is on sheet 2 and is
only used for reference.

On Sheet 1, I want to do an estimate. If I could remember the EXACT spelling
of every item, I could just enter the item number and do a lookup of the
price. However, since I cant remember the exact description of each part, I
need a way to look it up based on the first few letter or something.

As I mentioned, a drop-down ALMOST does this, but the problem is I get all
500 parts in the drop-down and it's tedious to sift through.

Is there a way to have it start eliminating choices as I start typing? For
example If I start typing "Br" it begins showing all the items that start
with "Br?"

It doesn't have to be exactly like this, but hopefully I have provided
enough information to convey the idea.

THANKS!
 
S

Shane Devenshire

Hi,

Data Validation does not has that built in feature, however you can program
it to do this. Or you could use VLOOKUP with wildcards, although that may
not meet your needs.

Suppose you are typing into A1, you can use
=VLOOKUP(A1&"*",Sheet2!Table,2,False)

Or you can look at the www.Contextures.Com web site for the VBA solution.
 
T

T. Valko

You can also just use the combo box from the control toolbox. "Piggybacking"
a data validation drop down list and the combo box together isn't necessary.
 
R

Rod

Thanks Shane,

It works in the sense that it will lookup the fist item it comes across, but
it doesn't bring up ALL the matches. I think you're right that the solution
will be VBA or maybe even ACCESS.

Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to keep formula and be able to input value in cell? 0
Can I increase the view of a drop down list beyond 8 in Excel? 2
Line copy - Not just a simple copy paste 3
Formatted cell column in drop down list 1
Validation list with formula? 3
function to return position in a list 2
Validation List 2
Depedent Dropdown and INDIRECT($P$8) with a twist 4

Top