| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to restrict user to input only one of multiple cells | Excel Worksheet Functions | |||
| Restrict-Filter-Limit-Validate user input in Excel | Excel Discussion (Misc queries) | |||
| how do I restrict cell input to dates only? | Excel Discussion (Misc queries) | |||
| Restrict input into cell array | Excel Discussion (Misc queries) | |||
| Restrict input if amount is exceeded | Excel Worksheet Functions | |||
Linear Mode
