You can quickly create a named range in Excel, but it doesn’t automatically expand to include new items that are added at the end of the list.
However, a dynamic Excel named list grows automatically, so here’s how to create that type of list.
Create a Dynamic Named Range
If you plan to add new items to a list, create a dynamic named range, by using an OFFSET formula.
Here are the steps:
- On the Ribbon, click the Formulas tab
- Click Define Name
- Type a one-word name for the range, e.g. PriceListDynamic
- Leave the Scope set to Workbook.
Add the Formula
Next, in the Refers To box, enter an Offset formula that defines the range size, based on the number of product names in column B
In this example, the following OFFSET formula is entered in the Refers to box:
=OFFSET(Prices!$B$1,1,0,COUNTA(Prices!$B:$B)-1,2)
How It Works
Here’s how the OFFSET formula works:
- Using cell B1 as the reference cell, the list will start 1 row down, and 0 columns to the right.
- It counts all the items entered in column B, and subtracts 1, because the list won’t include the heading in B1.
- The list will be 2 columns wide.
Then, as new items are added to the list, the dynamic named range will automatically expand to include them.
Video: Dynamic Excel Named List
To see the steps for creating a quick named range and a dynamic named ranged, you can watch this video tutorial.
_______________
Hi Debra,
Thanks for all your insight, it has helped me a ton. Here is my challenge:
I have many dynamic named ranges in a sheet; call each one a section. Each section/range is over columns A thru E. I’ve created a check box for each section/range, and if the check box is checked it shows the section/range, and unchecked Hides the range.
Using the offset and counta I can define each section as a range.
The problem is if I insert a row into any section/range, it pushes the last row of the section/range out of the section/range…
So what I need to do is define the start and end of each section/range, and allow that section/range to grow from the middle.
Both of these 2 lines (2 different options) will define my 1st section/range; but they don’t grow from the middle:
=OFFSET(‘RRC 2’!$A$4,1,0,COUNTA(‘RRC 2’!$A$4:$A$16)-1)
=OFFSET(‘RRC 2’!$A$4,1,0,COUNTA(‘RRC 2’!$A:$A)-41)
Please advise.
Thank YOU!
GJ
Hi Debra,
I just realized that each section has blank and populated cells, and if that number changes (a current blank cell gets data), then the range is thrown off as well.
So I am now more confused…
Is there a way to define the dynamic range that grows from the middle regardless if a row/column has data or not?
Thanks Again,
GJ
Is it possible to have the range of columns to be updated automatically as well, instead of fixed number (2 in this case)?
This is not working for one sheet and is working for another. On the sheet it doesn’t work the print range is based on the equation (correctly), but the range is not dynamic and gets converted to a strictly set notation (precisely, =OFFSET(PKG_num_Pass,-1,0,MAX(OFFSET(PKG_num_Pass,1,0,500,1))+4,16) is converted to =’Passivation Log’!$C$2:$R$7). Does anyone know why this may be happening?
In case anyone is also having this issue, this has been partially answered and is probably more likely to get a complete answer than on this website.
Love the youtube video explaining this, but I have an issue. I’ve created a dynamic named range in my worksheet, and several cells reference the dynamic named range using the following formula:
SUMPRODUCT(SUMIF(INDIRECT(“‘”&MonthsDynamic&”‘!d9:d101”),$D8,INDIRECT(“‘”&MonthsDynamic&”‘!f9:f101”)))
The purpose of the formula above is to look at any worksheet that has a name that matches a name in the named range, and return the value that matches the criteria in cell D8 (or the appropriate cell).
Only one (1) of the cells will return a value even though the majority of them should. Any idea what I may be doing wrong?
And the entire purpose of my workbook is a check register with tabs named for each month, and each month I would add the new month to the dynamic named range
Hi
I want to create dynamic list for each row.
Eg. Accout 1 has 5 Contract , Account 2 has 3 Contract Account4 Has 7 contract and so on…
Need to give drop down box on each row for account and based on account selection contract should be selected.
Please can somebody help