Create Dynamic Sub-Property Type Drop-Down Menus in Excel
A few years ago, I created a tutorial on building smart drop-down menus in Excel using dynamic named ranges and data validation lists. This offered a great way to have drop-down menus in your model that could be easily changed by the user to fit their specific needs.
But what about when you want a sub drop-down menu that changes based on the value selected in the main drop-down menu? This is common with property type and sub-property type lists. The values in the sub-property type drop-down menu need to change based on the value in the Property Type cell.
In this tutorial, I’ll build on what you learned in the smart drop-down menus in Excel tutorial to show you how to create dynamic sub-property type lists in real estate financial modeling.
Are you an A.CRE Accelerator member? Be sure to check out the various Advanced Concept modules focused on creating user-friendly models. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.
The sub-property type list changes based on the property type selected.
An Example of a Dynamic Sub-Property Type Drop-Down Menu
This technique can be used in other situations, but in real estate the most common scenario where you’ll use this is when adding a Sub-Property Type input. I’ve used this technique in various models over the years, but the best example is in my Single Tenant Net Lease Valuation Model. And so in this tutorial, I’ll use the methodology utilized in that model to accomplish this task.
Learn more about and download the free Educational Version of my Single Tenant Net Lease Valuation Model.
Step 1: The Setup – Inputs and Backend Data
Before modeling this, we need to first add inputs to the front-end and create a place to house the data and perform the calculations in the back-end.
In the case of this model, I have two front-end inputs: Property Type and Sub-Property Type. Those inputs are housed on the ‘Summary’ tab and are intended to be drop-down menus with pre-defined options to choose from.
The data and calculations on the back-end are stored on a ‘Data’ tab. That data tab has a ‘Property Type’ section and a ‘Sub-Property Type’ section. Each section includes inputs for the user to change the values in the front-end menus, as well as a calculation section to set the active ‘Sub-Property Type’ list based on the input entered for Property Type on the front-end.
The Data tab includes a Property Type section and a ‘Sub-Property Type’ section for back-end inputs and calculations
Step 2: Create Property Type List and Named Range
With the front-end and back-end ready, we then create a Property Type list. This is done on the Data tab, with pre-defined values entered as blue font cells to allow future users to change the pre-defined values to work with their own templates.
Once the Property Type List has been created, with blank space left below to allow for more values to be added in the future, a dynamic named range is created. Refer to my ‘Smart Drop-Down Menus in Excel‘ tutorial for how to create dynamic named ranges.
A dynamic named range is created for the ‘Property Type’ list
Step 3: Create Sub-Property Type Lists
Once the Property Type List and Property Type dynamic named range is done, we then create Sub-Property Type lists.
The title of each Sub-Property Type list is the Property Type, followed by a list of blue font inputs for Sub-Property Types under that Property Type. So for instance, a ‘Retail’ Sub-Property Type list, an ‘Office’ Sub-Property Type list, etc.
To make the Sub-Property Type lists fully dynamic, link the title to it’s respective value in the Property Type list. Thus, if the user changes from ‘Retail’ to ‘Hotel’ as their Property Type, the title in column D for Retail would automatically change to Hotel.
Step 4: Create Active Sub-Property Type List and Named Range
With dynamic named ranges created for each of the Property Type and Sub-Property Type lists, the next step is to create an active Sub-Property Type list. When I say active, what I mean is this dynamic list will show the Sub-Property Type values for the Property Type selected by the user on the front-end.
To do this, we first link the title of this active Sub-Property Type to the ‘Current Property Type’ selected by the user. In the case of my Single Tenant Net Lease Valuation model, I created a named cell called Property Type that links to the Property Type input on the front-end Summary tab. And thus, the title of this list is the Property Type named cell.
Next, above the title I use a MATCH() function to ask, which column within the Sub-Property Type list section contains the Active Property Type. In this case, that formula is as follows:
=MATCH(Property_Type,$D$8:$K$8,0)
The result of that formula is the column number, in the range D8:K8, where the Property Type value is shown. So for instance, Retail is column 1, Office, column 2, Industrial column 3, etc. If the user selects ‘Office’ as the active Property Type, the MATCH() function will output 2.
In this case, ‘Office’ is selected as the active Property Type. Thus the combination of MATCH() and INDEX() functions returns the list of Sub-Property Types under the Office Property Type.
With the title and column # formulas written, I then write a series of formulas using the INDEX() function to list out the Sub-Property Types. In this case, the first formula in the list is as follows:
=IF(INDEX(D9:K9,1,$M$7)=””,””,INDEX(D9:K9,1,$M$7))
The INDEX(D9:K9,1,M7) logic says: pull the value located in range D9:K9 that corresponds to row 1, column X of the range; where X is the value in cell M7 (i.e. the MATCH() function cell). So if M7 shows a 2 (i.e. Office), than the INDEX() function would pull row 1, column 2 of the range.
In this case, ‘Office’ is selected as the active Property Type. Thus the combination of MATCH() and INDEX() functions returns the list of Sub-Property Types under the Office Property Type.
That INDEX() function is copied down, with the result being all Sub-Property Types of the active Property Type. A dynamic named range is then created for that ‘Sub-Property Type’ list. Again, refer to my ‘Smart Drop-Down Menus in Excel‘ tutorial for how to create dynamic named ranges.
The end result is a Sub-Property List named range that is both dynamic to the values within the Sub-Property Type list as well as to the active Property Type.
Step 5: Add Name Ranges to Data Validation Lists
With two dynamic named ranges created, one for Property Type and the second for Sub-Property Type, we finalize this technique by adding those named ranges to the input cells on the Summary tab. Again, I went over this in my Smart Drop-Down Menus in Excel tutorial, but I’ll quickly repeat here.
- Go to the cell where the user will input Property Type and select that cell
- On the Data ribbon, in the Data Tools group, click Data Validation.
- On the Settings tab, in the Allow box, select List.
- In the Source box, type = followed by the name of your dynamic named range (e.g. =List_PropertyType)
- Click OK, and your data validation will be applied to that cell.
Repeat the above steps for your Sub-Property Type list input. And with that, you’ve created a dynamic Sub-Property Type drop-down menu!
Video Tutorial on How to Create Dynamic Sub-Lists in Real Estate Modeling
To supplement the written tutorial, I’ve created a walk-through of an instance where I use Sub-Property Type lists. The values in the Sub-Property Type list change dynamically, depending on the Property Type chosen by the user.
As always, if you have questions, comments, or would like to just say hello, don’t hesitate to reach out.
Frequently Asked Questions about Creating Dynamic Sub-Property Type Drop-Down Menus in Excel
What is the purpose of a dynamic sub-property type drop-down menu in Excel?
The purpose is to create a sub drop-down menu whose values change based on the selected main drop-down value. This is commonly used in real estate modeling to dynamically filter Sub-Property Types based on the selected Property Type.
Where are the drop-down menu inputs and data stored in the model?
The inputs (Property Type and Sub-Property Type) are on the ‘Summary’ tab, while the data and formulas are stored on the ‘Data’ tab, which includes both Property Type and Sub-Property Type sections.
How is the Property Type list created and made dynamic?
The Property Type list is manually input on the Data tab with room to add more entries. A dynamic named range is then created to allow the list to expand automatically. This range is used for the drop-down on the Summary tab.
How are Sub-Property Type lists created for each Property Type?
Each Property Type has its own Sub-Property Type list. The title of each Sub-Property list dynamically links to the corresponding Property Type. Beneath each title are the sub-categories in blue font, allowing for customization.
How is the “Active” Sub-Property Type list generated dynamically?
The active list is generated by:
Linking the list title to the selected Property Type
Using a MATCH() function to find the correct column
Using INDEX() functions to pull the corresponding Sub-Property Type values into a dynamic list
This list reflects only the sub-categories of the selected Property Type.
What formula is used to pull values from the Sub-Property Type list?
The formula used is:
=IF(INDEX(D9:K9,1,$M$7)=””,””,INDEX(D9:K9,1,$M$7))
This pulls values from the correct column using the column number output from MATCH() and skips blanks.
How is the dynamic drop-down menu applied in the input cells?
Using Excel’s Data Validation:
Select the cell
Go to the Data ribbon > Data Validation
Choose List
Enter =List_PropertyType or the appropriate named range for the Source
Repeat the steps for the Sub-Property Type using its dynamic named range.
What is the benefit of linking Sub-Property Type titles to Property Types?
Linking titles ensures that if the user updates a Property Type, the corresponding Sub-Property Type list title updates automatically, keeping the structure dynamic and flexible for different property types.
Where can I see this methodology in use?
This technique is used in the Single Tenant Net Lease Valuation Model shared by the author. A free educational version is available for download to explore the implementation firsthand.
