Data Validation‎ > ‎

SelfMerging DV List

"I have two lists of options that must be maintained separately.  They can expand and contract independently.  I'd like a 3rd list to create itself to be used for a data validation list that encompasses all the values from the other two lists.  It needs to maintain itself."

  1. All data validation lists are maintained on a single page (not an ultimate requirement, but makes the formulas easier to understand.)
  2. Third list creates itself with no VBA as first two lists are edited
  3. Data validation list should have no blanks or spaces even though the lists may be separate lengths
            Sample workbook:  SelfMergingValidationList.xls

                EXAMPLE 1

  1. The formula found in cell C2, then copied down as far as needed would be:

    =IF(ROW(A1)<=COUNTA($A$2:$A$100), INDEX($A$2:$A$100,
    ROW(A1)), IF(ROW(A1)<=COUNTA($A$2:$A$100)+COUNTA($B$2:$B$100),
    INDEX($B$2:$B$100, ROW(A1)-COUNTA($A$2:$A$100)), ""))

  2. A data validation list would have too many "blanks" in the list if the range were hardcoded. Let's create a named range that will show all the values in column C and no blanks.
    • Click in Insert > Name > Define
    • Create a named range called MergedList with the following formula:

  3. Use that named range MergedList in a cell as the data validation list source, like cell F3 in the sample sheet:

    Nothing says "thanks" like a steak dinner!
    PayPal - The safer, easier way to pay online!