Double Dynamic Named Ranges in Excel

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:
  • 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.

Click for more Useful Excel Hacks

%d bloggers like this: