Dynamic Names

You can create dynamic names that represent, for example, a range where the area can change as data is added or removed.

Create a name

Here I'm going to create a dynamic range for the range that begins in cell C4. I will call the range DynRange.

As C4 will always be the top-left cell I will give it a name DynRangeTopLeft.

To create the dynamic range called DynRange:

1. From the menu: Insert > Name > Define

2. In the "Names in workbook" box type "DynRange"

3. In the "Refers to" box type "=OFFSET(DynRangeTopLeft,0,0,COUNTA(Sheet1!$C:$C),COUNTA(Sheet1!$4:$4))"

4. Click on "OK"

Offset can have five arguments. Here we are using three - the first, fourth and fifth. For this example the second and third must be set to zero.

The first argument is "DynRangeTopLeft" which gives OFFSET its starting point.

The fourth argument is "COUNTA(Sheet1!$C:$C)" which counts the number of rows in use in column C.

The fifth argument is "COUNTA(Sheet1!$4:$4)" which counts the number of columns in use.

Using a dynamic name

Here is an example using DynRange in VLOOKUP

But ..

Dynamic names are very useful but with one major drawback - a dynamic name in a closed workbook can't be used by any open workbooks.

When the source workbook is open it works just fine:

But when the source workbook is closed: