A great way of making your macros and code more robust is to use dynamic named ranges. These automatically adjust and resize as the underlying data changes and I use them whenever I can.
In this beginner level tutorial we start with a completely blank spreadsheet. We build a number of lists within a worksheet and then define a number of named ranges.
When defining these named ranges we put some formulas in the “Refers To” box that:
1. Use the Excel OFFSET function (and we explain how to use it);
2. And use the COUNT or COUNTA Excel functions;
Combining these Excel functions in the “Refers To” formula allows us to define a dynamic named range. As a final step we then build a dropdown list within a worksheet cell that adjusts based on the dynamic named range.
The video uses MS Excel 2016 but the same steps are used for MS Excel 2010 and Excel 2013.
If you enjoyed this short tutorial then please add a comment, start a discussion, “Like”, “Favourite” or “Share” the tutorial and subscribe to my channel.