Data Validation From 2 Lists EXCEL 2010

Question

I have two sets of dynamic lists, List1 & List2

Lists are created using OFFSET and COUNTA, so new entries are contained within the list etc.

In Cell A1 i want to use the data validation list function, but I want it to reference BOTH of these lists,

I was told this would need to be done by VBA ?

Any suggestions ?

I currently use:

=(COUNTIF(list1,A1)+COUNTIF(list2,A1))>=1

In the 'Custom' section, but this does not allow me to have the list drop down option

Answer 1

you can use a macro to force the values into a single list

Sub Validation()

        Dim a$, el As Range
 ' SET THE LIST RANGE, YOU CAN USE MORE THAN 2
        Dim rng1 As Range, rng2 As Range

 'DEFINE THE CELLS USED FOR THE LIST, NAMED RANGE OR INDIRECT CAN BE USED
        Set rng1 = Range("List1")
        Set rng2 = Range("List2")


 'COLLECT VALUES FROM LISTS
        For Each el In rng1
            a = a & el.Value & ","
        Next

        For Each el In rng2
            a = a & el.Value & ","
        Next

 ' SET DESTIANTION OF DATA VALIDATION RULE
        With Range("A1").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=a
        End With

            Set rng1 = Nothing
            Set rng2 = Nothing

   End Sub

The only drawback is, the macro would need to be run each time you added a value to your list, you could put this in via a workbook change maybe