Create dynamic dependent dropdown list with dynamic name range in Excel by formulars only

admin


Context

What I want to achieve:

I need to create an Excel template that enables the team members to submit information. It's a multiple-layer structure and requires using the dropdown list to ensure consistency. 

Difficulties

  1. In the template, the list of category and sub-category is not exhaustive or stable. The team members would need to add new items to the list. 
  2. The template will be shared in cloud space and changes should be made through online Excel editor to avoid version collisions. Online Excel Editor doesn't support VBA, so only formulas can be used.

Approach Exploring

       For the purpose of referring:
        - Category Area refer to column A
        - Subcategory Area refers to Column C:D
        - Item Area refers to Columns F:H

  1. Dynamic Name Range
    It refers to when someone add/delete an item to the list, the range will change automatically. You can use OFFSET + COUNTA functions to do this. In the example for Category range, the formula is:
    =offset(sheet!$A$2, 0, 0, counta(sheet!$A:$A)-1,1)

    After this, in the field of category selection for column C in the subcategory area, use Data Validation to restrict the value of the cell. 

  2. Dynamic Dependent Dropdown List with fixed named range
    Now in the item area, when I choose a category value, I want to show only the corresponding subcategories instead of all the subcategories. If the subcategory area is fixed and is not going to change, this should be quite simple.
    1. create the FIXED range for "Beverage" list and "Food" list. Bear in mind that the Category Name should be exactly same as the string of the options, e.g. If you put the range name of beverage as "beverage", it won't work for the next step.


    2. In the Item Area, Use INDIRECT function in the data validation to restrict the value dynamically. Basically, the formula means it will translate the value of the Category cell to the address of that range. So the dropdown will show only the items in that range
    3. There is a big issue here. The name for a range should not contain any space. So if the strings of the category options contain space, it won't work. To solve this, you should replace the space in the strings with other Characters like underscore _. But this would be quite troublesome when large group of team will edit on the same file. 
  3. Dynamic Dependent Dropdown List with Dynamic named range
    The problem with fixed named range is that it is FIXED. When there is any change (eg. add/delete a cell) in the Sub-Category Area, you would need to re-do all. It is time-consuming. So how could we do that.
    Through the above-mentioned discussion, we can see that as long as we have the address of the subcategory range, we can use the indirect function to get the dynamic list. So actually, we don't necessarily need define all the named range. We this in mind, here is the solucion:
    1. Add a column next to Category in the category area. I named it as "Sub-Category Range" for this case
    2. Use the MATCH function to get the position of the first and the last cell of subcategory that belongs to the specific category
    3. Concatenates the strings to get the full address of the subcategory range, either using CONCAT function or & is fine.
      the complete formula for this case is:
      ="D"&MATCH(A3,C:C,0)&":D"&MATCH(2,1/(C:C=A3),1)
    4. Use INDIRECT and VLOOKUP functions in the data validation to get the dynamic dropdown list


Last Thoughts

This is not fully automatic.
  1. You need to drag down the formula whenever there is new category item. But you can just turn these area into Table, and then Excel will automatically extend the formular for you.
  2. You need to sort by subcategory in the subcategory area to make sure that the subcategories of one specific category are listed continuously. Otherwise, the value in "Sub-Category Range" will contains cells that doesn't belong to that category. This is the only thing can't not be automated by Excel functions. But sorting is just one-click in Excel. So I'm quite happy with the current solution. 

0 Comments