1

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

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
2
  • yeah this works fine, would prefer not to use the macro, but hey it works !!
    – PeterH
    Sep 29, 2017 at 9:02
  • Yup, it really works. Used this one to make validation from a list + empty position here - stackoverflow.com/questions/50100033/…
    – Vityata
    Apr 30, 2018 at 12:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .