Wednesday, August 18, 2010

Dynamic Ranges in Excel - low maintenance

I like Named Ranges in Excel. They free you from remembering to $A$1 every time you need to refer to an absolute block. And Named Ranges allow you to set up a reference block away from the worksheet that contains the Data Validation in-cell drop down list.

However, one more thing, Named Ranges still don't autoexpand to handle more items added later. Use ranges overlaid onto Lists (Excel 2003) or Tables (Excel 2007 and later). Or use OFFSET and COUNTA formula directly in the Name Manager specification.

in reference to: Excel Video Lessons | Dynamic Validation (view on Google Sidewiki)

No comments: