I have assisted many retailers with data models that involve product hierarchies. One of the most common questions I have heard is how to allow a user to make a selection in one drop-down menu and then have a second drop-down menu change to reflect allowable selections.
In this example, a fashion retailer has three departments (Women, Men and Kids) and within each are categories. Let’s take a look at the data:
There are 8 categories of merchandise in the Women’s department, 6 categories in Men’s and 5 categories in Kids.
The goal is to create two drop-down menus. The first menu allows a user to select one of the three departments and the second menu will automatically allow category selections from the chosen department.
The Department Drop-Down Menu
Excel has a great feature for validating data input called Data Validation. This button is located on the Data tab of the ribbon. If you place the selection in cell H2 then click on Data Validation, the Data Validation dialog box will appear. On the Settings tab is an Allow: drop-down and it should be set to List in order to create a list of allowable departments. Next, the Source textbox needs to know where to get the values for the list, so select the cell range =$A$1:$C$1. Click OK and now cell H2 is a drop-down menu that only accepts one of the three department names. Type “Department” in cell G2 as a label for the drop-down menu.
Two Important Things To Know
In order simplify this example, now is a good time to capture two pieces of information. The selected department appears in which column? How many categories exist for the selected department?
To figure out which column contains the selected department information, type the following function in cell E2: =MATCH(H2,A1:C1,0). This function will lookup the selected department in cell H2 and then search the range A1:C1 to find the value and return its position in the range. The third argument is a zero and it asks for an exact match. If you select the Kids department in cell H2, then cell E2 should return a 3.
To count the number of categories in the selected department, type the following function in cell E3: =COUNTA(INDEX(A2:C9,0,E2)). This function will look at the A2:C9 range then return all rows (the number 0 represents all rows) for the column number in cell E2. For this dynamic cell range, the COUNTA function will count how many cells contain a category value. If you select the Men’s department, then cell E3 should return a 6.
The Category Drop-Down Menu
The final task is to create a category drop-down menu that understands which department was selected. Select cell H3 so that another data validation list will appear there. Click on Data Validation and from the Settings tab allow another List. The Source this time will not be a fixed range such as =$A$1:$C$1 but will be a dynamic range. An OFFSET function can be typed into the Source text box. Type in the following: =OFFSET($A$1,1,$E$2-1,$E$3,1). This function starts at cell A1, then moves down one row, then moves to the right E2-1 columns. Once at this cell, the dynamic range will contain the number of rows from E3 with 1 column of width. If you make a selection from the category drop-down menu, you should only be able to choose categories within the previously selected department. Add a label in cell G3 for “Category”.
When you test the two drop-downs, the finished example should look like this:
If you have any questions for future articles, would like to know more about using Excel, Access Databases, VBA, Charts, need a dashboard, data model, database solution or analytics training, please contact Scott Cuthbertson:
Email: scottjcuthbertson@gmail.com
LinkedIn: http://ca.linkedin.com/in/scottcuthbertson
Blog: http://theanalyticspro.blogspot.ca/
Scott Cuthbertson BBA
Scott is an Analytics Professional who has worked with over 100 clients in the retail and financial services industries. He has helped organizations improve their labour scheduling, inventory replenishment, performance management, organization design and pricing. He has been a practitioner of analytics since childhood, using data models to win Hockey Pools. His expertise lies in developing data models, reporting, algorithms and solutions for any organization that wants to use their data to improve decision making. He also teaches at the CMA Professional Development Institute.
© Scott Cuthbertson 2014 Blog: Words From The Analytics Pro
