Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Problems in Comma Separated List | Excel Worksheet Functions | |||
Add Values in a comma separated cell to combobox | Excel Programming | |||
Turning column contents into text list separated by a comma | Excel Discussion (Misc queries) | |||
validation list with comma values? | Excel Programming | |||
Going from column to comma separated list... | Excel Discussion (Misc queries) |