Validation list source is a list of comma separated values in a single cell

[dave.c...@computalog.com]

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

[Auric__]

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.

[dave.c...@computalog.com]

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

[dave.c...@computalog.com]

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)

[GS]

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

[dave.c...@computalog.com]

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

[GS]

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.

[dave.c...@computalog.com]

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

[GS]

> 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.