Assuming it is acceptable to have the master table sorted and both tables in the same worksheet, the simplest solution entails just a relatively short data validation formula.
I have set up my example worksheet as follows:
Enter this formula as the source of the In-cell dropdown List data validation of
The beauty of this formula is that it adapts to changes in the master table, including inserting a new row at the top and appending a row after the last one.
The disadvantages are:
- The master table must be in the same worksheet
- The master table must be sorted
- Other tables on the sheet cannot safely intersect the Domain column unless they are guaranteed not to contain values matching the master table's Domain values
More complex solutions exist to overcome all these limitations.
Okay. Just to keep Rajesh S happy, (and prove him wrong ;-) um, no, you don't need an array formula to either generate a unique list, or generate a dependent/filtered list, if the source list is sorted), here is the worksheet updated to include the creation of the unique domains list and the dropdown validation formula in column
D that uses it:
The normal, non array-entered formula entered into
C2 and then filled down is:
The data validation formula for