DYNAMIC NAMED RANGES

DYNAMIC NAMED RANGES

http://www.cpearson.com/excel/named.htm

Dynamic Ranges

It is often useful to create a name that refers to a range of cells, where the range depends on the content of the cells. For example, you may want a name that refers to the first N non-blank entries in column A. Excel's Name tool allows you to do this. For example, creating a name called DynaRange, referring to

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

If the first 20 rows of column A contain data (and the rest are blank), DynaRange will refer to the range A1:A20

See the on-line help for the =OFFSET function for a description of the arguments. Setting the Width argument to 2 will allow us to use this name in a =VLOOKUP function

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

Then, call VLOOKUP with the DynaRange argument for the lookup range:

=VLOOKUP(C1, DynaRange, 2)

As data is added to columns A and B, the range search by VLOOKUP will extend to include the new data.

Download a workbook illustrating dynamic ranges.

http://www.beyondtechnology.com/geeks007.shtml

1. 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. 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)

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.

The second argument indicates how many rows to move from the anchor address to begin the range. In this case, we used 0 which would include my header row. If we substituted 1 then the header row would be skipped and the range would begin on row 2 of the table.

The third argument indicates how many columns to move from the anchor address to begin the range. In this example we used 0, meaning to begin the range on the same column as the anchor address provided in the first argument.

The fourth argument tells how many rows the range should extend. They key here is to substitute the COUNTA function for your primary field, instead of hardcoding a value. This way if you add or remove items from that field, the range will grow or shrink accordingly. You also may need to subtract 1 from the COUNTA result to account for the elimination of a header row. It is important that you do not have any superfluous data beneath the table range you intend to evaluate.

The fifth, and final, argument is how many columns wide you want for the dynamic range to be. In our example, this range includes only a single column, therefore the argument provided was simply 1.

This is a very flexible technique for defining your named ranges. The best thing to do is to experiment with some variations of the sample formula provided and you will soon find that Dynamic Named Ranges will become an indispensable tool you will want to use throughout your Excel work.

Note:

Dynamic named ranges can only be referenced in an open workbook, because they are dependent upon a workbook's ability to calculate.

http://www.ozgrid.com/Excel/DynamicRanges.htm

For ALL examples you need to:

· Fill Column A with a mix of text and numeric entries.

· Go to: Insert>Name>Define and in the Names in workbook box type any one word name (I will use MyRange) the only part that will change is the formula we place in the Refers to box.

· 1:Expand Down as Many Rows as There are Numeric Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)

· 2:Expand Down as Many Rows as There are Numeric and Text Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

· 3:Expand Down to The Last Numeric Entry
In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)
If you expect a number larger than 1E+306 (a one with 306 zeros) then change this to a larger number.

· 4:Expand Down to The Last Text Entry
In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

· 5:Expand Down Based on Another Cell Value
Put the number 10 in cell B1 first then:
In the Refers to box type: =OFFSET($A$1,0,0,$B$1,1)
Now change the number in cell B1 and the range will change accordingly.

· 6:Expand Down One Row Each Month
In the Refers to box type: =OFFSET($A$1,0,0,MONTH(TODAY()),1)

·7:Expand Down One Row Each Week
In the Refers to box type: =OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)
Requires the "Analysis Toolpak" to be installed. Tools>Add-ins-Analysis Toolpak