Scroll the menu to find all the places to visit
Excel - Dynamic Named Range
27 Apr 2009I’ve recently been helping some colleagues develop some reporting templates and they had a very particular requirement around drop down lists (which I’ll probably post about at some point in the future). One issue that did occur however was that the dynamic lists that were incorporated into the workbook were added to and even though I had mentioned to the users about using Excel’s name manager to redefine the range of cells with that name, they forgot and inevitably the workbook would end up back with me to rectify.
I decided that I’d try and find a
way of defining a range so that it would automatically extend itself
when another cell entry was added to the bottom of the list. It turned
out to be more straight forward than I’d initially thought it might be.
The statement below sets a range that will begin at cell “A2” on
worksheet “Sheet1” and extend down for the number of cells that contain
a text entry. It assumes that the list is unbroken and that the top
cell is the name of the list (hence the -1 after the “COUNTA”). Whilst
the name is not essential I include it as having the name of the list in
the top cell allows me to programatically carry out list operations by
referencing the top cell in a column.
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
The screen shot on the right shows a sample list of
colours that has a range defined by using the function above. A drop
down list is automatically populated from this list and is shown in cell B2.
