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.
