"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."
- 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 edited
- Data validation list should have no blanks or spaces even though the lists may be separate lengths
Sample workbook: SelfMergingValidationList.xls EXAMPLE 1
- The formula found in cell C2, then copied down as far as needed would be:
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.
Use that named range MergedList in a cell as the data validation list source, like cell F3 in the sample sheet:
- Click in Insert > Name > Define
- Create a named range called MergedList with the following formula:
Nothing says "thanks" like a steak dinner!