Create a dynamic named range
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.
Table of Contents
1. Create a dynamic named range
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.
1.1 How to create a named range
- Press with left mouse button on the "Formulas" tab on the ribbon.
- Press with left mouse button on "Name Manager" button, a dialog box "Name Manager" appears.
- Press with left mouse button on "New..." button to create a new named range and name it.
- Type the formula below in "Refers to:" window:
- Press with left mouse button on the "Close" button.
1.2 Named range formula
1.3 Explaining formula
Step 1 - Count non-empty cells in column A
The COUNTA function counts non-empty cells in a given cell range or array.
COUNTA(value1, [value2], ...)
COUNTA($A:$A)
returns 3. There are three values in column A.
Step 2 - Count non-empty cells in row 1
COUNTA($1:$1)
returns 4. There are four values in row 1.
Step 3 - Create cell reference to last non-empty cell
The INDEX function returns a value based on a row and column number, however, it can also be used to create a cell reference.
INDEX(array, [row_num], [column_num], [area_num])
INDEX($1:$1048576, COUNTA($A:$A), COUNT($1:$1))
becomes
INDEX($1:$1048576, 3, 4)
and returns D3.
Step 4 - Create cell reference to the entire cell range
The colon character lets you combine two cell references and create a larger cell ref to a cell range.
$A$1:INDEX($1:$1048576, COUNTA($A:$A), COUNT($1:$1))
and returns $A$1:$D$3.
1.4 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)
2. List all named ranges and their cell references
This article shows you a way to display all named ranges you have in a workbook. This is a powerful feature in Excel if you have many named ranges and Excel defined tables and you want a better overview.
Instructions on how to build a list with all your named ranges:
- Go to "Formula" tab on the ribbon.
- Press with mouse on "Use in Formula" button on the ribbon.
- Press with mouse on "Paste Names...", a dialog box appears.
- Press with left mouse button on "Paste List" button on the dialog box.
- The list of named ranges is created.
The image below shows the named ranges in the "Name Manager" dialog box, they correspond to the list show in the image above.
Named range category
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]
This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]
Excel categories
12 Responses to “Create a dynamic named range”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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)))
Get the Excel example file
named-range-formula.xlsx
[...] more about named ranges: Create a dynamic named range in excel Get the 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, press with left mouse button 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 […]