Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default Show comma in data validation list?

Would someone please help. Thanks.

I want the following 3 items in a data validation list. Note they have
commas in the items.

Apple, Kiwi, and Orange
Strawberry, Mango, and Banana
Pineapple, Banana, and Orange

I can't get data validation to display the comma in the item because it
takes the comma as a seperator for a new item. So typing the above show
the following list:

Apple
Kiwi
and Orange
Strawberry
Mango
and Banana
Pineapple
Banana
and Orange

I have to be able to type these items directly into the DV list, not
into cells where DV uses a cell reference.

Can this be done?

Thanks again.

  #3   Report Post  
Gregg
 
Posts: n/a
Default

Perfect. Thanks a lot, Debra!

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

You are a cheater <vbg.

That's not really a comma.

And if the user always chooses the value using the dropdown, then this won't
matter. But if the user types in a valid entry, the user will have to type in
that alt-0130 instead of a comma.

Or if there's a formula that refers to that cell, it can't use the real comma.



Debra Dalgleish wrote:

In the Data Validation dialog box, for the commas that you want to
appear in the item, hold the Alt key, and on the number keypad, type: 0130

wrote:
Would someone please help. Thanks.

I want the following 3 items in a data validation list. Note they have
commas in the items.

Apple, Kiwi, and Orange
Strawberry, Mango, and Banana
Pineapple, Banana, and Orange

I can't get data validation to display the comma in the item because it
takes the comma as a seperator for a new item. So typing the above show
the following list:

Apple
Kiwi
and Orange
Strawberry
Mango
and Banana
Pineapple
Banana
and Orange

I have to be able to type these items directly into the DV list, not
into cells where DV uses a cell reference.

Can this be done?

Thanks again.


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


--

Dave Peterson
  #5   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

I prefer the term "creative" <g.

But you're right about the other stuff -- if no formulas are based on
the cell contents, the error alert could be turned off, and the values
could be typed with a real comma.

Or, programming could convert the fake commas to real commas.

Dave Peterson wrote:
You are a cheater <vbg.

That's not really a comma.

And if the user always chooses the value using the dropdown, then this won't
matter. But if the user types in a valid entry, the user will have to type in
that alt-0130 instead of a comma.

Or if there's a formula that refers to that cell, it can't use the real comma.



Debra Dalgleish wrote:

In the Data Validation dialog box, for the commas that you want to
appear in the item, hold the Alt key, and on the number keypad, type: 0130

wrote:

Would someone please help. Thanks.

I want the following 3 items in a data validation list. Note they have
commas in the items.

Apple, Kiwi, and Orange
Strawberry, Mango, and Banana
Pineapple, Banana, and Orange

I can't get data validation to display the comma in the item because it
takes the comma as a seperator for a new item. So typing the above show
the following list:

Apple
Kiwi
and Orange
Strawberry
Mango
and Banana
Pineapple
Banana
and Orange

I have to be able to type these items directly into the DV list, not
into cells where DV uses a cell reference.

Can this be done?

Thanks again.


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





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



  #6   Report Post  
Gregg
 
Posts: n/a
Default

Yup, I found that out a bit ago. ;-)

We use the commas as delimiters in a search routine. So, I'll either
extend the code to include the alternative comma as delimiters or write
an event that changes the alt-commas to true commas.

Thanks to both of you for your help.

  #7   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

In your first post, you said you had to use a delimited list, but could
you use a named list on a hidden worksheet? Then, you could include the
commas, and not have to worry about programming.

Gregg wrote:
Yup, I found that out a bit ago. ;-)

We use the commas as delimiters in a search routine. So, I'll either
extend the code to include the alternative comma as delimiters or write
an event that changes the alt-commas to true commas.

Thanks to both of you for your help.



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

  #8   Report Post  
Gregg
 
Posts: n/a
Default

Yeah, I thought about that early on, but I wanted to avoid using
another area.

My simplest solution so far is to abandon commas alltogether and us "/"
instead. So and item would look like this...

Apple / Orange / Pear

Luckily none of my items include that delimiter. I can then use that
character easily in search and count routines and the user can easily
edit the DV list directly in the dialog box.

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
Data validation, cell protection or other method? KG Excel Discussion (Misc queries) 5 June 17th 05 05:22 AM
Data Validation Mike R Excel Discussion (Misc queries) 11 May 6th 05 02:38 AM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM
Data Validation list selection question Bob Wall Excel Worksheet Functions 2 December 4th 04 05:51 PM
Data Validation List Option Affecting Other Cells? tomrobs Excel Worksheet Functions 0 November 5th 04 08:13 PM


All times are GMT +1. The time now is 06:57 PM.

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

About Us

"It's about Microsoft Excel"