Data Validation in Excel gives us the power to turn any cell into a drop-down menu, so our users can only choose the entries from that list. However, it’s often critical to lock in the contents of that list to an ever-changing list of entries (like people, locations, products, etc.). To do this, you need to create a dynamic reference to the list. In the old days, you had to use the OFFSET function, but Table references in Excel take away the need to use OFFSET (it’s a complicated function – believe me!).
Recently, though, I’ve been working with some clients with some HUGE datasets, and they need to make their spreadsheets as small and efficient as possible. One way to solve this problem is to get rid of volatile functions – functions that need to be recalculated constantly. Unfortunately, simply using Table references with Data Validation requires the use of INDIRECT, which is volatile. So, I found a solution to the problem that cuts all of that out – referencing a Table range with a named range. In this tutorial, we demonstrate how to create the perfect dynamic drop-down menu.
Topics covered include:
- Data Validation
- Named Ranges
Download the spreadsheet we used in this exercise here: Dynamic Drop-Down Menus