Dynamic Named Ranges in Excel

I want to share a tip I read about (and used) today for Dynamic Named Ranges in Excel. I've used Excel for a long time, and spent many many many hours building spreadsheets that mimic little databases because a client or user couldn't support a database but really "wanted" a simple spreadsheet. I often use named ranges for data validation to substitute for lookup tables and foreign key constraints. A common problem is when the users go to add a new value to the bottom of the lookup table the new value would fall outside the named range and not be used in the lookup table.

I've tried teaching users, writing instructions on how to expand the range, and had fallen in to the habit of making the last element be a row of dashes ("-----") with instructions to "add a new entry to the list by inserting a row ABOVE the dashes." Not very elegant at all, but it did usually work.

The nice folks at ozgrid.com gave a good, simple explanation for how to create Dynamic Named Ranges that will automatically expand to include new values added at the end of the list. Great time saver!