Defined Name as Cell Reference

[TheWaterDog]

Forgive me if this has been answered somewhere; I've looked and can't seem to find a solution on the board.

I'm using dependent dropdowns in Sheet 1 [Main] via an indirect() in Data Validation from named ranges in Sheet 2 [DVS].

So, you choose the "Parent_Choice1" in Cell A...and then...
Cell B offers only the Named Range "Child_Choices" related to Parent Choice1.

Seriously, this little beauty has literally saved WEEKS of time in completing rows of data (there's 50 parents with up to 50 child choices for each so to be able to only offer the related info has been excelly-life-changing)...but I'm looking ahead and the issue is going to be the next job. The Named Ranges are currently specifically for this job - i.e. "Child Choice1" is 'Rendering_Works'.

How.....if possible....when naming a range, can you reference the cell (currently "Rendering_Works") and NOT the text? When defining the name, it won't allow reference to the cell instead of writing the text...and everything I've looked at with dynamic name ranges is "kinda slightly not exactly that".

Anyone have any idea how to convince excel to allow a cell reference instead of text - so that ultimately, the wookbook can be used as a template and the text changed to suit the job and the Named Ranges to update automatically?

 

[TheWaterDog]

So, I think I've found a work-around for this just in case anyone is reading.
Essentially, the answer is no - Named Ranges are just that; named - and if you want to change it going forward, there's no real way of doing it unless you manually rename each range to match the new title.
So I just created a macro that grabbed everything and "Created From Selection" using the top row.
It's not exactly an solution - but it's good enough to enable the sheet to be saved as a template without the Named Ranges and then once completed (with all the new titles for the new project), you just run the macro to name everything from the new data entered.


That'll do Pig, that'll do.