This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent column. There is no vba code in the example demonstrated here.
The following named ranges expands automatically when new text values are added.
The first array formula returns unique distinct values in cell G1:L1 from named range Region. The second array formula in cell range G2:L7 returns coherent values from each region. All of these calculations can be hidden or placed on a calculation sheet.
Array formula in cell G1:
Array formula in cell G2:
How to create an array formula
- Select a cell
- Type array formula
- Press and hold Ctrl + Shift
- Press Enter
Copy cell G1 and paste to cell range H1:L1. Copy cell G2 and paste to cell range G2:L7.
Now it is time to create the two last named ranges that will be used in data validation lists.
Data validation lists
- Select cell range D2:D9
- Go to tab "Data"
- Click "Data validation" button
- Select "List"
- Type =UniqueRegion
- Click OK
Repeat above steps with cell range E2:E9 and Source: =UniqueProduct.
Download excel *.xlsx file