Data Validation list from multiple columns.

[mabubakerali]

for example: Lets say i have data list in column a and data list in column B now i want apply data validation list in cell C1, Now i want C1 to drop down the list which includes the data from column a and column b in ascending order like it start from cell a1 and end on cell B5. Please help. I have also uploaded the file in case any one want to use that to help.

 

[rlv01]

If you want to do it using a formula, you may need to create a helper column combining col A & B. For example, you could put this formula

=VSTACK(A1:A5,B1:B5)

in Cell D1 and then reference the new list in col D (D1:D10) when defining your data validation rule.


Alternatively, you could opt for a VBA solution

Sub RefreshValidation()
    Dim AA, AB
    
    With ActiveSheet
        AA = .Range("A1:B5").Value
        AB = Evaluate("ARRAYTOTEXT(TRANSPOSE(A1:B5))")
        
        With .Range("C1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=AB
        End With
    End With
End Sub