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.
If you plan to add new items to a list, create a dynamic named range, by using an OFFSET formula. In this example, the formula entered in the Refers to box is:
=OFFSET(Prices!$B$1,1,0,COUNTA(Prices!$B:$B)-1,2)
- 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.
Watch the Video
To see the steps for creating a quick named range and a dynamic named ranged, you can watch this video tutorial.
_______________
Hi Debra:
I like the idea of using a Dynamic Named Range, however, for the workbook that I am using now, this range is in the middle of the sheet, with data above it.
Is there a way to handle a situation like this.
Thanks – I appreciate your insights into Excel, I use your tips all of the time.
Hi Stan, if you know which row the range starts in, you could subtract everything above that. For example, if the range starts in B31, and that position won't change:
=OFFSET(Prices!$B$1,30,0,COUNTA(Prices!$B:$B)- COUNTA(Prices!$B1:$B30),2)
Or, name the start cell for the range, and use that to find and count the cells above it:
=OFFSET(Prices!$B$1,ROW(StartCell)-1,0,COUNTA(Prices!$B:$B) – COUNTA(OFFSET(Prices!B1,0,0,ROW(StartCell)-1,1)),2)
I love these and use them a lot, but recently I have been rethinking how much I use them. Typically I use these together with a list via data validation so that the drop-down list updates appropriately. But then I found out that using a dynamic range doesn't restrict user entry in the cell. If you have a hard coded range, then you can set up data validation to only accept items in that list. If you try to type anything else, it will throw an error and tell you to fix it. With dynamic ranges, you can type whatever you want in the data validation cell, even if it doesn't match the list, and you will get no error.
Annoying.
Jayson, the data validation lists based on a dynamic range should block any invalid entries, unless there's a blank cell in the dynamic range. Check your formula to make sure you aren't including an extra row.
Debra- That was my thought at first. Would you care to try and let me know what you find? I've just tried again tonight. I don't see a blank row (no blanks in the data validation drop-down).
Jayson, can you send me your file or upload it somewhere that I can access it?
ddalgleish AT contextures.com
Debra,
I totally agree with your post. Since I first know of this thanks to Chandoo's post, I have never stopped using it.
Stan, maybe you'd like to consider split the contents of your sheet in 2 different ones: one with the data and one with the summary or the remaining contents. It helped me on structuring both macros and formulas, and it's easier to understand and maintain by someone else if the chance arises, proving your professionalism.
Rgds,
Martin.
Debra, in my brilliant comment ;) at the link below, I added a technique to deal with an empty list – a list with just a header: http://www.dailydoseofexcel.com/archives/2010/01/07/new-years-resolution-no-more-offset/#comment-43246.
Martin, you're right, separate sheets certainly make things easier to maintain.
Doug, thanks for the link! That's a nice solution to the problem of deleted heading rows.
One problem I have with dynamic name lst is when I create a pivot table using query (to combine various sheets), these names does not seem to be recognised by MS Query.
HI Debra, I need to do the same thing but instead of adding rows to columns, I need to dynamically add columns to a single row. For example, column A starting in cell 1 has 25 rows of time intervals. Each day I need to add the previous days numbers to the next empty column. I have that figured out, but I would like to use a named range for each of the 25 rows and have them dynamically expand as I would like to use the named ranges in reports I have created based on this data. So each row must have it's own unique named range that expands across columns. Can you use a variation of the code above to accomplish this? if not, can you shed some light on how it might be done. Love your site, thanks
@Boon, you might have to use a non-dynamic named range, and use a macro to redefine the range after you've added or deleted data.
@wayne, you can use a COUNTA function to calculate the number of columns too. For example, count the cells with data in row 1:
=OFFSET(Prices!$B$1,1,0,COUNTA(Prices!$B:$B)-1,COUNTA(Prices!$1:$1))
Does anybody use Data>Lists (or Tables in 2007) for dynamic data validation lists? I'm just fooling around with it. After creating the List, I created a named range, using only the column of the list that I want in the data validation, and excluding the header.
This seems to work well, with the following benefits:
- Can have more than one list on a Validation Lists worksheet (each one having its header in Row A) and am only allowed to insert/delete rows from one List at a time. (With more than one dynamic range on a sheet it's easy to accidentally delete a whole row spanning multiple ranges.)
- Am prevented from deleting a list's header row.
- The named range expands and contracts with the list as I add or delete rows, just like a dynamic range.
- The data validation range isn't affected if you add other items below the List in the same column.
- Deleting all the items in the list results in a blank as the only data validation choice – the named range adjusts to include the "*" row of the List. However, when I add items back in the List, the "*" row is no longer included, and the blank Data Validation choice goes away.
- the named range is visible in the Name box
I'd be interested to know if people use this method. I don't recall seeing it mentioned.
Doug, the List and Table features certainly have many benefits, as you mentioned. However, many of my clients still have some people using Excel 2002 or earlier, so I can't risk using those features, since they aren't supported.
Thanks Debra. After I posted my comment I realized that I'm only working with 2003 or later right now, but that the issue is exactly what you said for earlier versions. Other than that, Lists/Tables seem to work very well in place of dynamic ranges for DV. I've written some VBA code to convert a range to a List and then add a named range to the first column for this purpose.
Hi.
Thanks for the great tip.
I'm trying to implement dynamic lists (on a third dependent list http://www.contextures.com/xldataval02.html#Third) using Excel 2003 and it doesn't seem to be working. Does Excel 2003 allow for dynamic named lists?
Thanks,
Aviva
Thanks Aviva, and to use a dynamic list in dependent data validation, you can follow the instructions here:
http://www.contextures.com/xldataval02.html#Dynamic
Hey Debra
Great post, I have a question though and at the risk of being asking one too complicated for a comment.....
What I would like to do is have primary and dependent lists that update automatically. The primary list is a list of phases, the dependent list is a list of sub phases. The user chooses a phase in column A, which then dictates which list they will choose from in column B. I want users to be able to add/edit/delete both phases and sub phases as they see fit since this is a living document. They way I am doing it now is through a recorded macro that uses insert>name>create, but it adds all the empty cells to the list.
Thanks for any help
Johannes
Dear Debra,
My name is joju andi am working in a construction company i would like to learn step by step for applying dinamic name range i have some datas of employees like
Code no Name Designation
2343 Mr.A Construction Manager
2345 Mr. B Project Engineer
Like this the data base will be added with new code no and employees in thease cases how can i use dinamic name range
Hello, we use excel to record employee time worked. When a employee works over 40 hours in a given week, we require a formula that will distinguish when the total hours total over 40 and place that (additional over 40) value into a different cell which will then calculate time and a half of pay for each hour of over time.