Data Validation‎ > ‎

SelfMerging DV List

 PROBLEM:"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."SPECIFICATIONS:All data validation lists are maintained on a single page (not an ultimate requirement, but makes the formulas easier to understand.)Third list creates itself with no VBA as first two lists are editedData validation list should have no blanks or spaces even though the lists may be separate lengths            Sample workbook:  SelfMergingValidationList.xls                EXAMPLE 1HOW TO: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)), ""))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 > DefineCreate a named range called MergedList with the following formula:       =OFFSET(\$C\$2,0,0,MATCH("*",\$C:\$C,-1)-1,1)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!