Named ranges are among the most powerful features of Excel, especially when used as the source range for list controls, PivotTables, or charts. A problem arises, however, when the contents of a list change often. It would be a problem to have to redefine your named ranges everytime a table has records added or removed. The solution is to create a range that will automatically adjust based on the number of items in the list.
First, create a list in column A of a worksheet.
If you are working on a version EARLIER than Excel 2007 :
From the worksheet’s Insert menu choose Names then the Define…. Enter a name for your new range, such as MySheet!rngDynamic. Then, in the Refers to: box, enter the following:
=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)
The user interface changed with Excel 2007. So instead, from the Formulas menu go to theDefine Names group and create this named range fron the Define Name command.
How It Works:
The first argument for the OFFSET function is the cell on which you want to anchor it. Everything else will be set relative the this cell address. Typically, you will want it to be either the header for the first field in your source data table or its first record.
CAclubindia
