I had a spreadsheet with a long list of products (just under 300) each of which was tagged to a brand grouping. Some of the brand groupings could have 35 individual products. I wanted to give the user a way of first selecting the brand and then selecting one of the products within the brand.
The solution I came up with was to use two data validation boxes
- The first data validation list allowed the user to select the brand using the dynamic range name ‘Brand’ see dynamic range names for more on creating these)
- The second data validation allowed the user to select the product. The challenge for this was to create a list that varied with the brand being selected from the first data validation selection. In the define name dialogue box I came up with the following formula to be entered in the range box:
=OFFSET(INDIRECT(ADDRESS(6+MATCH(Sheet1!$C$4,Sheet1!$C$7:$C$300,0),4)),,,COUNTIF(Sheet1!$C$7:$C$300,Sheet1!$C$4),1) - Some points of explanation:
- $c$4 is where I had the brand selection
- $C$7:$C$300 is the column in the data with the brand tagging
- the ADDRESS(6+MATCH(Sheet1!$C$4,Sheet1!$C$7:$C$300,0),4) part of the formula creates a text address which is used by the INDIRECT() as the starting point for the offset. The Match() part of this identifies where the selected brand starts.
Excel Monkey 