Got too many opinions for my own good and no one to listen to them so here we are! Plus random musings on the world through the eyes of a grumpy old limey, things that just plain interest me and a bit of actual knowledge here and there.
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.