 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007Data Validation List created from a text in a Cell
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 [quoted text clipped - 3 lines] > cells and then using the multiple cells as source to the Data > Validation. 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 >> separated text present in another cell? The text in this cell is [quoted text clipped - 11 lines] > > in the Formula text box appears to do what you asked. 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 On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)" <rickNOSPAMn...@NOSPAMcomcast.net> wrote: > >> 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 [quoted text clipped - 26 lines] > > Rick 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 >> >> separated text present in another cell? The text in this cell is [quoted text clipped - 33 lines] > This solves the validation problem but does not display the drop down > as a list does. 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.  SignatureBiff Microsoft Excel MVP > On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)" > <rickNOSPAMn...@NOSPAMcomcast.net> wrote: [quoted text clipped - 38 lines] > Regards > Gap 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 > 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 [quoted text clipped - 90 lines] >> Regards >> Gap 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: > 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 [quoted text clipped - 119 lines] > >> Regards > >> Gap Nice and compact but this also results in a run-time error 1004 if you clear cell A1. SignatureBiff Microsoft Excel MVP > or just put this one in sheet tab module > [quoted text clipped - 151 lines] >> >> Regards >> >> Gap 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 > Nice and compact but this also results in a run-time error 1004 if you > clear cell A1. [quoted text clipped - 156 lines] >>> >> Regards >>> >> Gap 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. SignatureBiff Microsoft Excel MVP >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 [quoted text clipped - 174 lines] >>>> >> Regards >>>> >> Gap No doubt, that's a better approach. One problem, though. If you clear cell H1 then you get a 1004 run-time error. SignatureBiff Microsoft Excel MVP > 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 [quoted text clipped - 120 lines] >>> Regards >>> Gap 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 > No doubt, that's a better approach. > [quoted text clipped - 128 lines] >>>> Regards >>>> Gap |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.