Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 23rd 05, 09:02 PM
 
Posts: n/a
Default 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


  #2   Report Post  
Old June 23rd 05, 09:18 PM
Debra Dalgleish
 
Posts: n/a
Default

There's a sample workbook on my web site that shows a product name and
code in the Data Validation dropdown list. After an item is selected,
the cell shows only the product name. You may be able to adapt this to
your workbook.

On the following page:

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

Under the data validation heading, look for 'Data Validation "Columns"


wrote:
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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #3   Report Post  
Old June 23rd 05, 10:08 PM
[email protected]
 
Posts: n/a
Default

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

  #5   Report Post  
Old June 25th 05, 03:48 PM
[email protected]
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatic Data Validation drop down creation Buddhapenguin Excel Discussion (Misc queries) 1 May 12th 05 08:41 PM
trying to create an (almost) circular formula between cells and data validated cells with lists KR Excel Worksheet Functions 0 May 12th 05 07:21 PM
Data Validation DLM Excel Discussion (Misc queries) 2 February 22nd 05 03:26 AM
Effect of Conditional Formatting, Data Validation Bill Sturdevant Excel Discussion (Misc queries) 1 January 26th 05 12:50 AM
how to add two data validation lists to a single cell? Mike Peter Excel Worksheet Functions 1 December 8th 04 09:22 PM


All times are GMT +1. The time now is 09:44 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017