If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
data validation lists
Hi,
I am trying to use data validation lists.... with a small twist. For simple validations, I have no trouble creating the list, or setting up the validation because that's pretty standard. Right now, though, I want to do something a little nonstandard. I have a validation list that consists of a code and a description. So, for a list of states I would fill the validation list with stuff like 'NY New York' where 'NY' is the state code, and 'New York' is a description of what the code means. I create a validation list with all the state codes and their full names, and everything is fine. When the user clicks on the pull down list they see a list of states and their names. But, when the pull down is NOT ACTIVE, I want the cell to show only the state code (NY) and not the complete contents of the validation list (NY New York). Seems like it should be possible, but I'm definitely not an excel expert, so this is something I haven't figured out yet. Any ideas? Thanks in advance... Brad |
#3
|
|||
|
|||
I took a very quick look and I think this is exactly what I want. I
noticed you answer quite a few excel questions, and wanted to tell you that I appreciate your willingness to help! Thanks ! Brad Vernon |
#4
|
|||
|
|||
You're welcome! If you have any trouble modifying the code, post another
question, and someone will probably be able to help. wrote: I took a very quick look and I think this is exactly what I want. I noticed you answer quite a few excel questions, and wanted to tell you that I appreciate your willingness to help! Thanks ! Brad Vernon -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
Debra,
I had no trouble getting your code to work in my situation. Thank you. There is a new twist that I would like to resolve. In most types of pull down lists you can enter a character and have the list automatically advance to a matching entry. I don't get this type of behavior. Also, if, in your example, I simply wanted to type the word Desk rather than select it from the pull down I would get a validation error. My supervisor noticed that my code exhibited this undesirable characteristic. Have you put together a list that would allow either selection via pick from list or typein of the value (desk) itself? Note that if I type in 'Desk -- ID# 40732' (per your example) it would work. But, clicking in this cell fails the edit test later..... Another way of looking at this is to have the validation itself key on 'Desk', but have the pull down DISPLAY the longer text "Desk -- ID# 40732". Then typing desk would work.... any ideas? Regards, and thanks again, Brad |
#6
|
|||
|
|||
You could combine the product/code technique with the combobox overlay,
described he http://www.contextures.com/xlDataVal11.html wrote: Debra, I had no trouble getting your code to work in my situation. Thank you. There is a new twist that I would like to resolve. In most types of pull down lists you can enter a character and have the list automatically advance to a matching entry. I don't get this type of behavior. Also, if, in your example, I simply wanted to type the word Desk rather than select it from the pull down I would get a validation error. My supervisor noticed that my code exhibited this undesirable characteristic. Have you put together a list that would allow either selection via pick from list or typein of the value (desk) itself? Note that if I type in 'Desk -- ID# 40732' (per your example) it would work. But, clicking in this cell fails the edit test later..... Another way of looking at this is to have the validation itself key on 'Desk', but have the pull down DISPLAY the longer text "Desk -- ID# 40732". Then typing desk would work.... any ideas? Regards, and thanks again, Brad -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
6 Data Validation lists depending on 1 cell value | beel | General Discussion | 9 | June 10th, 2005 07:34 PM |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
Data Validation List, how to create sub lists??? | Sam | Worksheet Functions | 1 | October 1st, 2004 02:41 PM |
using data validation with several different lists | Brian | Worksheet Functions | 2 | September 29th, 2004 08:57 PM |
Using Named Ranges with Data validation | Kevin | General Discussion | 3 | June 20th, 2004 10:34 PM |