Data Validation question

[mcomp72]

I am trying to create data validation where the choices the user has is limited to what is in another column. I've created a simple example so you can see what I mean.

In Column A are my "categories". In Column B are the specific "choices". Each choice belongs to a category.

(On a different sheet I will have the data validation drop-down, but for the sake of this example I've put them on the same sheet.) I will have a column that has a row for each category that exists (Column D in my screenshot). In the next column I want there to be a drop-down list in each row, where the choices are from Column B, but the only choices listed should be where the value in the Column D cell matches Column A. For instance, the only choices for Fruit should be Banana & Apple.

To make it more complicated, the number and names of the categories will be changing, and the number of choices will be changing. So I need this to work dynamically.

I have looked at many web pages and forum posts about how to do this, but all of them are pretty complicated. They all require me to create "helper" columns. For instance, I could create a column for Fruit, another for Veggie, and another for Dairy. In each column I could have it list only the choices for that category. But since the number of categories will be changing, that becomes a little complex to deal with. Before I go that route, I wanted to make sure there wasn't an easier way to do it. It seems like there should be a way to do what I am wanting to do without any helper columns. Perhaps there's a formula I could use in the data validation that would do it? I don't have a lot of experience with data validation, so I don't quite understand all the ins & outs of it.

Any help would be appreciated.

 

[HighAndWilder]

I am trying to create data validation where the choices the user has is limited to what is in another column. I've created a simple example so you can see what I mean.

View attachment 117771

In Column A are my "categories". In Column B are the specific "choices". Each choice belongs to a category.

(On a different sheet I will have the data validation drop-down, but for the sake of this example I've put them on the same sheet.) I will have a column that has a row for each category that exists (Column D in my screenshot). In the next column I want there to be a drop-down list in each row, where the choices are from Column B, but the only choices listed should be where the value in the Column D cell matches Column A. For instance, the only choices for Fruit should be Banana & Apple.

To make it more complicated, the number and names of the categories will be changing, and the number of choices will be changing. So I need this to work dynamically.

I have looked at many web pages and forum posts about how to do this, but all of them are pretty complicated. They all require me to create "helper" columns. For instance, I could create a column for Fruit, another for Veggie, and another for Dairy. In each column I could have it list only the choices for that category. But since the number of categories will be changing, that becomes a little complex to deal with. Before I go that route, I wanted to make sure there wasn't an easier way to do it. It seems like there should be a way to do what I am wanting to do without any helper columns. Perhaps there's a formula I could use in the data validation that would do it? I don't have a lot of experience with data validation, so I don't quite understand all the ins & outs of it.

Any help would be appreciated.

This could help you.

Set up two sheets. (Sheet1 and Sheet2)

This is Sheet1

Data Validation question.xlsm
	A	B
1	Fruit	Banana
2	Veggie	Squash
3	Dairy	Milk
4	Veggie	Green Beans
5	Fruit	Apple
6	Dairy	Cheese
Sheet1

Put this code into a Main Code Module.

It will create the validation lists downwards from the range specified by the rngValidationList range variable.

Where do you want to call this from and when?

Let me know if the positions of the validation lists are pre-set.

Public Sub subCreateValidationLists()
Dim rngList As Range
Dim Wb As Workbook
Dim dictUnique As Object
Dim arrList() As Variant
Dim i As Integer
Dim rngValidationList As Range
Dim strList As String
Dim x As Integer

  ActiveWorkbook.Save

  Set dictUnique = CreateObject("Scripting.Dictionary")
    
  Set Wb = ThisWorkbook
  
  ' Set the cell to have the first validayion list in it.
  Set rngValidationList = Wb.Sheets("Sheet2").Range("D4")
  
  ' Set the range to form the validation list.
  Set rngList = Wb.Worksheets("Sheet1").Range("A1").CurrentRegion
    
  arrList = rngList.Value
    
  For i = LBound(arrList) To UBound(arrList)
    If Not dictUnique.Exists(arrList(i, 1)) Then
      dictUnique.Add key:=arrList(i, 1), Item:=dictUnique.Count + 1
    End If
  Next i
  
  For x = 0 To dictUnique.Count - 1
        
    strList = ""
    
    ' Write the label on the left of the validation list.
    rngValidationList.Offset(0, -1).Value = dictUnique.Keys()(x)
    
    For i = LBound(arrList) To UBound(arrList)
      If arrList(i, 1) = dictUnique.Keys()(x) Then
        strList = strList & "," & arrList(i, 2)
      End If
    Next i
               
    ' Create the data validation in the cell.
    With rngValidationList.Validation
    
      .Delete ' Remove any existing validation.
      
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Mid(strList, 2)
      .InputTitle = "Select option."
      .ErrorTitle = "Error!!"
      .InputMessage = ""
      .ErrorMessage = "Please provide a valid input."
      
    End With
    
    Set rngValidationList = rngValidationList.Offset(1, 0)
      
  Next x
        
End Sub

 

[Peter_SSs]

Another suggestion (for Excel 365)

If you want, columns G, H, etc. can be hidden once the formulas have been entered.

24 10 07.xlsm
	A	B	C	D	E	F	G	H	I
1	Fruit	Banana
2	Veggie	Squash		Fruit			Banana	Apple
3	Dairy	Milk		Veggie			Squash	Green Beans
4	Veggie	Green Beans		Dairy			Milk	Cheese
5	Fruit	Apple
6	Dairy	Cheese
7
DV

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A1:A6)
G2:H4	G2	=TRANSPOSE(FILTER(B$1:B$6,A$1:A$6=D2))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E2:E4	List	=G2#

 

[mcomp72]

Thank you both for your replies.

H&W - My workbook needs to work on both Windows & Mac, and I'm not sure if CreateObject("Scripting.Dictionary") would work on Mac, so I've opted not to go with that idea. (I didn't have access to my Mac today to try it out.)

Peter - I ended up going with something similar to what you suggested. I tweaked it slightly, and I created a UDF to help make things a bit easier, but it's more or less the same.

Thanks again for both of your suggestions!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and you are correct - the Scripting Dictionary would not work on a Mac.