## Create a dynamic named range

*Article updated on February 24, 2018*

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves you time as you no longer need to adjust the cell reference in a formula.

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns instantly to the named range.

This makes the named range dynamic meaning you don´t need to adjust cell references every time you add a new row or column to the list.

The formula takes care of only one list per sheet.

### How to create a named range

- Click "Formulas" tab on the ribbon.
- Click "Name Manager".
- Create a new named range and name it.
- Type the formula below in "Refers to:" window:
- Click close button.

### Named range forumla:

### Explaining formula

**Step 1 - Count the number of cells in column A that are not empty**

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

becomes

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, 4, COUNTA(Sheet1!$1:$1))

**Step 2 - Count the number of cells in row 1 that are not empty**

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

becomes

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, 4, 3)

**Step 3 - Return a reference of the cell at the intersection of a particular row and column**

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

becomes

=Sheet1!$A$1:$C$4

This cell reference changes whenever new rows or columns are added or removed.

### Possible scenarios when to use named ranges

- Formulas, making them dynamic and easier to read and understand.
- Charts (How to create a dynamic chart)
- Pivot tables (Create a dynamic pivot table and refresh automatically in excel)

### Functions in this article:

** COUNTA(**value1,[value2],

**Counts the number of cells in a range that are not empty**

**)**

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]How to create a dynamic pivot table and refresh automatically

David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]Create a drop down list containing alphabetically sorted values

Overview This article describes how to create a drop down list populated with sorted values from A to Z. The […]Quickly create links to sheets, tables, pivot tables and named ranges in a workbook

A week ago I posted Create links to all sheets in a workbook and today I want to show you how […]List all named ranges and their cell references in a worksheet

This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]### 12 Responses to “Create a dynamic named range”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

The Count(A:A) approach assumes non blanks

To handle blanks you can try the below

Assume You need to create a 1D Dynamuic Name called ID_NO

Also Assume that the Column to have mixed data types : Numbers , Text and Blanks

aData={"Ω";9.9E+307}

ID_NO = Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(aData,Sheet1!$A:$A,1))

will take of blanks between data

sam,

interesting!

I can´t get it to work unless i change ID_NO to this:

ID_NO = Sheet1!$A$1:INDEX(Sheet1!$A:$A, MAX(MATCH(aData, Sheet1!$A:$A, 1)))

Thanks for your contribution!

Sorry my bad....Typo...I missied out the Max...

You could drop the match type as the default is 1

ID_NO =

Sheet1!$A$1:INDEX(Sheet1!$A:$A,MAX(MATCH(aData,Sheet1!$A:$A)))

Can I use this to specify the size of a table using the range of another table, which will increase in rows weekly?

Daniel,

I think so

Named range

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, ROWS(Table_name), COUNTA(Sheet1!$1:$1))

And how about if the table is in another sheet? Table1 in sheet1, has to match Table2 in sheet2

Daniel,

I don´t understand why you want a table in sheet1 to match a table in sheet2 using a named range?

How can i use this feature in a column that contains formulas?

Jimmie,

Try this formula:

=Calculation!$A$1:INDEX(Calculation!$1:$65535, MAX((Calculation!$A$1:$A$65000<>"")*ROW(Calculation!$A$1:$A$65000)), MAX((Calculation!$1:$1<>"")*COLUMN(Calculation!$1:$1)))

Download example file

named-range-formula.xlsx

[...] more about named ranges: Create a dynamic named range in excel Download excel *.xlsx [...]

Thank you Oscar. This is the most reasonable and workable function I have encountered for the outcome desired to date.

I can get the formula (=HYPERLINK("[CompletePull.xlsx]'Sheet2'!$E$"&MATCH(CHAR(255),Sheet2!E1:E1002,1)+1,1)) to hyperlink to another sheet within my workbook which is what I desire; however, my issue is that in this other sheet my column of data are dates. If it were text it seems to work great but not numbers. I have attempted to readjust the CHAR function within the MATCH nest to more accommodate my dates but it still doesn't work. It keeps hyperlinking to the first set of data in my column, not the next empty cell. I can have my users, click on the desired cell; however, I want my application (workbook) to be as simplistically functional as possible. Have you attempted to get your example to work with you set of data in column B? Do you have any other suggestions?

Thank you again

[…] template has dynamic named ranges for all chart series. This lets you change the date range and all chart data is adjusted […]