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!