Data Validation List created from a text in a Cell

  • Thread starter Thread starter gappodi
  • Start date Start date
G

gappodi

Hello,

Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Any cool hacks?

Thanks you.
Gap
 
Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.
Click to expand...

Assuming for this example that your selected range is A1:A10 with A1 the
active cell and that your comma delimited list is in H1, selecting Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

Rick
 
Whats the simplest way to create a data validation list from a comma
Assuming for this example that your selected range is A1:A10 with A1 the
active cell and that your comma delimited list is in H1, selecting Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.
Click to expand...

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick
 
I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick
Click to expand...

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Regards
Gap
 
Whats the simplest way to create a data validation list from a comma
Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.
Click to expand...

Sorry, I missed the "list" part of your question. I don't think you can do
it the way you want (with the list in a cell). You can do it if you put your
list directly in the Data/Validation dialog and maintain it there (in the
Data/Validation dialog box, select List from the Allow combo box and just
put your list... no equal sign... in the Source text box). While I haven't
looked into it yet, I am reasonably sure a macro can be developed to do what
you want... is a macro solution acceptable to you?

Rick
 
Here's a possible solution that uses an event macro. I'm not the best VBA
programmer so you should test this on a test file before you implement it in
your real file. It does work in my tests! All you good programmers out there
I would appreciate and *constructive* feedback on this approach.

Assume:

A1 = cell with comma delimited text. Like: Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a change in
cell A1. I'm assuming that cells to the right of A1 are empty so they will
accept the Text to Columns data. If these cells are not empty the TTC will
overwrite them. Then you can use a dynamic range formula as the source for
the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll post a
link.
 
I'm thinking an approach like this might be easier to implement. All that is
needed is to place this code in the worksheet code window and then type in a
comma separated list into the cell designated to hold it (assumed to be H1
for this example).

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Whenever the cell containing the comma separated list is changed (specified
in the ValidationList constant), the above macro will change the Data
Validation List for the designated range (stored in the ValidationRange
constant). I'm not sure if EnableEvents needs to be toggled on and off as
the Target range is not being being affected in any way by this macro. I'll
let others more familiar with that aspect of the macro world comment on the
need for it.

Rick
 
or just put this one in sheet tab module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
With Range("C2").Validation
.Delete
.Add xlValidateList, Formula1:=Range("A1").Value
.InCellDropdown = True
End With
End Sub


"Rick Rothstein (MVP - VB)" skrev:
 
No doubt, that's a better approach.

One problem, though. If you clear cell H1 then you get a 1004 run-time
error.
 
Good point! This patched code should handle that problem...

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Range(ValidationList).Value = "" Then
Range(ValidationRange).Validation.Delete
ElseIf Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Rick
 
I think it "looks" compact because he left out the Error Message coding and
did not use the Const(ant) definitions that I did (which I think make the
code easier to read and easier to maintain in the future should any changes
to the code be required). Oh, and he saved an End If statement by changing
the If-Then statement to exit the subroutine rather than using it to filter
the natural fall-through. With all that said, he actually has an extra line
of code to specify the InCellDropdown assignment which seemed to be covered
automatically within the code I posted. Don't get me wrong, I am not
knocking excelent's approach, just pointing out that the compact look comes
about as a result of omissions (which are not necessarily bad in and of
themselves).

Rick
 
I understand. It's the same considerations when writing a formula, how
robust does it need to be and when does robutness cross the line into bloat.
 
Back
Top