h1

Excel – Dynamic Named Range

27/04/2009

I’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.

Use name manager to define the dynamic range

Use name manager to define the dynamic range

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 -1after 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 prgramatically carry out list operations by referencing the top cell in a column.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Example list

Example list

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: