Can the name of a named range be dynamic?

I'd like the name of a named range in excel to be dynamic (as opposed to the range itself!). I'd like to know if/how I can define a range name using a formula or reference cell, so that the name changes when the content of the reference cell is changed.

E.g. If I write a list in a column and write the title of the list at the top of the column like so:

     A
1 *Colours*
2  Red
3  Yellow
4  Blue

And then name the cell-range of the list (A2:A4) after the title of the list (A1), I then want the range name to change automatically if the title of a list is changed (i.e. Range Name = A1 and the name changes if content of A1 changes).

Extra info about my specific case: I am using named ranges to create several dependant and dynamic dropdown lists in an excel spreadsheet that's for someone else to use. It's all set up so that if the user wants to add items to existing lists (in the supporting lists worksheet) then the dropdown lists (in the main table worksheet) automatically change. However, my next challenge is to make it easy for the user to add new lists. My plan is to provide spare list columns (in the supporting lists worksheet) already set up so that when it is filled in, it will turn automatically into a dropdown list (in the main table worksheet). All the data validation formulas (using named ranges) are set up to create the dropdown lists in the main table worksheet, the missing step is the automatic naming of the list ranges once the user enters a new list title. My data validation formulas need to use named ranges because the dropdown lists shown in the main table are dependent on the users previous selections.

I'd be very grateful for any hints!


This assumes that the value in A1 will be entered rather than set by a formula. Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim str As String
    str = Range("A1").Text
    If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        If n.RefersTo = "=Sheet1!$A$2:$A$4" Then
            n.Delete
        End If
    Next n
    ActiveWorkbook.Names.Add Name:=str, RefersTo:="=Sheet1!$A$2:$A$4"
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

EDIT#1:

to use A1 & B1 as the Name, just replace:

str = Range("A1").Text

with:

str = Range("A1").Text & Range("B1").Text