Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default Validation list source is a list of comma separated values in asingle cell

Is there a way of making the validation list reference a single cell but in that cell is the list of acceptable values which are comma separated. I think this would be simple in VBA but I am trying to figure out if there is formula that can be entered into the source field for the validation criteria that would then use these values as a list.

Thanks


David
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 538
Default Validation list source is a list of comma separated values in a single cell

dave.cuthill wrote:

Is there a way of making the validation list reference a single cell but
in that cell is the list of acceptable values which are comma separated.
I think this would be simple in VBA but I am trying to figure out if
there is formula that can be entered into the source field for the
validation criteria that would then use these values as a list.


Try this:

=IF(ISERROR(FIND(B1,A1)),"",B1)

A1 is your list, B1 is the item to look for. If you want some sort of
"invalid item" error, put it in the quotes. (This works because FIND()
returns #VALUE! (an error) if it can't find what it's looking for.)

--
Avoid use of a tourniquet, unless you're into that sort of thing.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default Validation list source is a list of comma separated values in asingle cell

I think I follow what you are saying but I will need to play around with it a bit more to determine if it will work for me.

Thank you


On Sunday, December 6, 2015 at 7:40:12 PM UTC-7, Auric__ wrote:
dave.cuthill wrote:

Is there a way of making the validation list reference a single cell but
in that cell is the list of acceptable values which are comma separated.
I think this would be simple in VBA but I am trying to figure out if
there is formula that can be entered into the source field for the
validation criteria that would then use these values as a list.


Try this:

=IF(ISERROR(FIND(B1,A1)),"",B1)

A1 is your list, B1 is the item to look for. If you want some sort of
"invalid item" error, put it in the quotes. (This works because FIND()
returns #VALUE! (an error) if it can't find what it's looking for.)

--
Avoid use of a tourniquet, unless you're into that sort of thing.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default Validation list source is a list of comma separated values in asingle cell

I played with this some more ... I place the following into the source for the validation list and it retrieves the list of comma separated values from the cell but the dropdown is only populated with this as a single item. It doesn't interpret them as a list of items - it sees them as a single string. Why would this be?

=OFFSET(INDEX(INDIRECT(RngName),1,1),0,-10)


On Monday, December 7, 2015 at 2:06:13 PM UTC-7, wrote:
I think I follow what you are saying but I will need to play around with it a bit more to determine if it will work for me.

Thank you


On Sunday, December 6, 2015 at 7:40:12 PM UTC-7, Auric__ wrote:
dave.cuthill wrote:

Is there a way of making the validation list reference a single cell but
in that cell is the list of acceptable values which are comma separated.
I think this would be simple in VBA but I am trying to figure out if
there is formula that can be entered into the source field for the
validation criteria that would then use these values as a list.


Try this:

=IF(ISERROR(FIND(B1,A1)),"",B1)

A1 is your list, B1 is the item to look for. If you want some sort of
"invalid item" error, put it in the quotes. (This works because FIND()
returns #VALUE! (an error) if it can't find what it's looking for.)

--
Avoid use of a tourniquet, unless you're into that sort of thing.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Validation list source is a list of comma separated values in a single cell

Is there a way of making the validation list reference a single cell
but in that cell is the list of acceptable values which are comma
separated. I think this would be simple in VBA but I am trying to
figure out if there is formula that can be entered into the source
field for the validation criteria that would then use these values as
a list.

Thanks


David


A DV list needs to be stored in a range where each cell in the range
holds 1 listitem. Thus, a delimited list in a cell is 1 listitem.

You need to spread your list across a row (or down a col) of cells!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default Validation list source is a list of comma separated values in asingle cell

Garry
Yes I understand that but Excel will also interpret an entry of a comma delimited list of values (10,20,30) in the source area and will present them as single entries in the dropdown. This is what I am trying to take advantage of.

If I place the formula I presented in my last post in a cell if returns the contents of the cell so I don't understand why the validation doesn't interpret it correctly.

David



David


A DV list needs to be stored in a range where each cell in the range
holds 1 listitem. Thus, a delimited list in a cell is 1 listitem.

You need to spread your list across a row (or down a col) of cells!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Validation list source is a list of comma separated values in a single cell

Garry
Yes I understand that but Excel will also interpret an entry of a
comma delimited list of values (10,20,30) in the source area and will
present them as single entries in the dropdown. This is what I am
trying to take advantage of.

If I place the formula I presented in my last post in a cell if
returns the contents of the cell so I don't understand why the
validation doesn't interpret it correctly.

David



David


A DV list needs to be stored in a range where each cell in the range
holds 1 listitem. Thus, a delimited list in a cell is 1 listitem.

You need to spread your list across a row (or down a col) of cells!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Two completely different issues!
Providing a range ref is handled differently than providing a delimited
*string list*! (Emphasis on the word "string")
Typing a delimited list persists the listitems as delimited.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default Validation list source is a list of comma separated values in asingle cell

Okay thanks I think I now understand why it doesn't work as I had expected. I am reverting to a VBA method instead.


On Tuesday, December 8, 2015 at 10:47:03 AM UTC-7, GS wrote:
Garry
Yes I understand that but Excel will also interpret an entry of a
comma delimited list of values (10,20,30) in the source area and will
present them as single entries in the dropdown. This is what I am
trying to take advantage of.

If I place the formula I presented in my last post in a cell if
returns the contents of the cell so I don't understand why the
validation doesn't interpret it correctly.

David



David

A DV list needs to be stored in a range where each cell in the range
holds 1 listitem. Thus, a delimited list in a cell is 1 listitem.

You need to spread your list across a row (or down a col) of cells!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Two completely different issues!
Providing a range ref is handled differently than providing a delimited
*string list*! (Emphasis on the word "string")
Typing a delimited list persists the listitems as delimited.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Validation list source is a list of comma separated values in a single cell

Okay thanks I think I now understand why it doesn't work as I had
expected. I am reverting to a VBA method instead.


Using VBA is an alternative, but will require you set the DV criteria
to a delimited string list.

Storing your list is a single row (or col) of contiguous cells is
another way to go. The row/col can be on the same sheet but hidden (I
usually put rows above my data, cols to the left). The list can also be
stored on a hidden sheet (This is my preference), in a dynamic named
range so it can be edited as needed without having to 'reset' the DV
criteria.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
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
Finding Problems in Comma Separated List Daren Excel Worksheet Functions 5 January 9th 09 06:22 PM
Add Values in a comma separated cell to combobox [email protected] Excel Programming 2 December 2nd 08 03:18 PM
Turning column contents into text list separated by a comma Kevin Rhinehart Excel Discussion (Misc queries) 1 January 16th 07 08:00 AM
validation list with comma values? Sebastian Excel Programming 4 September 24th 05 04:06 PM
Going from column to comma separated list... jmboggiano Excel Discussion (Misc queries) 1 March 10th 05 05:30 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"