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

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.

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.

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.

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

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

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

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

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