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:
Because it is worksheet code, it is very easy to install and automatic to use:
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:
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:
with: