Tips & Tricks For The Excel Power User

Excel Dynamic Drop Downs & Dynamic Named Ranges

Excel is a very powerful piece of software but with that power can come complexity. Data validation for cell data entry, controls on user forms or controls placed directly on worksheets often use lists as a data source. If you have a large project with multiple references to those lists maintaining the project can become tedious as the lists change. That maintenance can be much reduced by the use of dynamic lists and dynamic named ranges. With the use of these techniques the controls and dropdowns that reference the lists do not need to be reconfigured when the lists change. This article explains how to set up a data validation using a dynamic list and goes on to look at setting the list up as a dynamic named range that can be referenced by any data validation or control. These principles can be used for controls on spreadsheets, controls on user forms and pretty much anywhere in a project that a list is referenced. I use Excel 2007 and the screenshots are based on that version of Excel but these principles can be applied to other versions with little tweaking.

Continue reading and find out how on InfoBarrel