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

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!

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

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rod" wrote:

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!

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

Hi,

Specifically you should look at this

http://www.contextures.com/xlDataVal10.html

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rod" wrote:

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!

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

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.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Specifically you should look at this

http://www.contextures.com/xlDataVal10.html

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rod" wrote:

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!

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

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.

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rod" wrote:

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!

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

Hi,

You may want to refer to question 44 on the following link -
http://ashishmathur.com/replies.aspx.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Specifically you should look at this

http://www.contextures.com/xlDataVal10.html

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rod" wrote:

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!

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

Hi,

You may want to refer to question 44 on the following link -
http://ashishmathur.com/replies.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Rod" wrote in message
...
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!

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

Please ignore previous post

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Specifically you should look at this

http://www.contextures.com/xlDataVal10.html

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Rod" wrote:

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!