Excel – Can the name of a named range be dynamic

microsoft excelnamed-ranges

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!

Best Answer

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
Related Question