Dynamic Range Names

CREATING & USING DYNAMIC RANGE NAMES This is a really useful feature* in Excel and well worth learning! Q: When might you use a dynamic range name? A: Any time you're using a list or range of data in Excel in an operation like a chart, a list, a control, etc. and your list or range may change in size, either getting larger or smaller. Below is an overview of how to create a dynamic range name. Then see the worksheets that follow for examples. The syntax of the formula you need is: =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 3) The OFFSET function has 5 parameters. The first parameter establishes an "anchor cell". The second and third parameters indicate how many rows and columns to move from the anchor. For our purposes here, zero rows and zero columns. The fourth parameter establishes the number of rows in the range. The COUNTA function as used above counts cells with data in Column A. The fifth parameter indicates how many columns should be included in the defined range. Use Insert, Name, Define to enter a name for the dynamic range. Enter the OFFSET function in the "Refers to" box. For example:

*Thanks for this technique to this Excel tips website:

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

CREATING & USING DYNAMIC RANGE NAMES: With a Control The list is in Column A. The dynamic range name of the list is BRAND. How to define a dynamic range name for a list.

ADAMS ANELL BBIRD CAMPB DLMNT DNDCK DOLE GATRD GEN GLORT GOYA HANOV HEINZ HIC HPNCH HUNTS JCYJC KERNS KUNER LBGLD LIBBY LLEAF This is cool MOTTS MSLMN PL RDGLD S&W SACRA NEWBRAND

1st OFFSET argument: Anchor cell. Here A8. 2nd argument: How many rows to move from the anchor to begin the range. Here 0 rows. 3rd argument: How many columns to move from the argument to begin the range. Here 0 columns. So start the range at the anchor cell address: A8. 4th argument: How many rows are in the range. The COUNTA function counts the number of cells that contain data. A:A indicates that the count should be in Column A. (So don't have any extra data non-list data in column A!) 5th argument: How many columns wide the dynamic range should be. Here, 1 column wide.

Use a dynamic range name in a form control. To see how this works, try adding a new brand name at the bottom of the list or anywhere in the list. The new name should show up in the Listbox control. At left is a Control Toolbox (not Forms Toolbar) control. However, it works with the Forms Listbox (shown below) as well. This is cool

25

CREATING & USING DYNAMIC RANGE NAMES: With a Chart The list is in Columns B and C. The dynamic range name of the list is Numbers.

Jan Feb Mar Apr May Jun Jul Aug

28 32 44 39 58 40 22 19

x

0

Start the Chart Wizard without selecting a data range. In Step 2, enter the dynamic range name in the "Data range" box. Here, Numbers. Finish the chart as usual.

Chart Created from Dynamic Named Range

In this case, you must add new entries into the range in the middle of the range. Adding a row of data at the end of the range doesn't update the chart. I've added a placeholder last row to the Numbers list used here. See the next worksheet (Try2b) for a fix. Defining the 2-column dynamic list range:

60 55 50 45 40 35 30 25 20 15 10 5 0 Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

x

CREATING & USING DYNAMIC RANGE NAMES: With a Chart, This worksheet describes how to use dynamic range names with a chart so that you can add data to the end of the range and have it automatically included in the chart. Fuqua Harvard Wharton Tuck UNC Stanford Michigan

98 78 85 79 72 96.3 90

Method 1. For the example data at left, define two separate dynamic nam I've named the first range School and the second range Use the usual Insert, Name, Define steps with OFFSET and COUNT. 2. Create a line chart of the existing data. 3. Select the series marker in the chart and look in the formula ba to find the SERIES function. Replace the static range references in that formula with the School 4. Add a line of data at the end of the list. The chart updates. Delete a line of data; the chart updates as well. Dynamic Range Source: Add to End of List w/Automatic Update

* Thanks to Excel Hack Extraordinaire Jim Smith for this improvement on implementing dynamic ranges in charting.

100 95 90 85 80 75 70 65 60 Row 1

h a Chart, Improved*

fine two separate dynamic named ranges. ol and the second range Rank. teps with OFFSET and COUNT.

chart and look in the formula bar place the static range the School and Rank dynamic names. the list. The chart updates. updates as well.

ange Source: w/Automatic Update

CREATING & USING DYNAMIC RANGE NAMES: With a Pivot Table The list is in Columns B, C, and D. The dynamic range name of the list is Products. I can't get the dynamic range name to work for Pivot Tables… Let me know if you find out how! Category Product Unit Price Beverages Chang $24 Beverages Chai $20 Beverages Milk $2.45 Beverages Water $1.50 Condiments Catsup $1.59 Condiments Soy Sauce $3.00 Condiments Oyster Sauce $4.25 Condiments Sesame Oil $6.82 Confections Pavlova $18.00 Confections Meringue $4.45 Confections Chocolate $8.52 Confections Nougat $3.50 Defining the 3-column dynamic list range:

a Pivot Table