Dynamic Named Range based on second column's value without VBA

Question

I'm trying to build an interface that allows a user to select a category of modification (17 total categories) and have the second drop down box list only those modifications (anywhere from 5-30 modifications) that apply to that category.

Normally, I would set up reference tables to indicate which modifications apply to which category. However, this particular tool is going to have new data populated every week by a non-technical person. There is already a structure in place that will assign each modification number to a given category. However, the list of modification numbers present in any given data pull will change so much that maintaining the separate lists will get overly cumbersome.

My hope is that there is a way to have a dynamic named range contain only those values where the category column is equal to the selected category.

I want to use DNRs because I will be using it as part of a validated data list that will end up controlling the display of a handful of graphs for the end user. Additionally, we aren't allowed to use macros for our shared spreadsheets.

• A1 would contain the category (I can get this named range to work just fine, it's a basic one)
• A2 should contain only those mods from column C where the corresponding cell in column D equals the value of A1.

Answer 1

I did something similar recently, where the subcategories had to be user-updated. The categories and subcategories are easily updateable and easily understood by the non-technical, the only rule being that you don't leave blanks in the middle of your lists. In my working version, of course, the category table was on a separate "Menu" tab.

This is (a slightly simplified version of) the layout I came up with:

And these are the named ranges in the example:

category is the category cell cats is the row that holds the list of available categories DDCats is the list of categories for the drop-down, dropping blanks mods is the full grid of subcategories/modifications modlist is the dynamically chosen column of mods, dependent on the chosen category DDMods is the list of mods for the drop-down, dropping blanks

Answer 2

You can use a dynamic range name to pull the sub categories if

• there is a two-column table with categories in one column and sub categories in the other column
• all main categories are repeated (no blanks)
• the table is sorted ascending by the main category column.

A dynamic range name can then be built with a formula along the lines of

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,1))

So, you only need to work out how to get this two-column table created dynamically from your data entry tool.