Okay, This is where the magic happens: The Indirect formula
So now we have a list, we know how to name ranges but we need the list to look at the contents of other cells to populate itself. The way we do this with the Indirect formula
So you will need to have all the lists written already and have given them unique names for this formula to work
How I do it is pretty simple
First, I create a simple list in the first column. Just a regular data validation list – nothing fancy but this serves as the basis for the rest of the lists. The entries in this list are the named ranges for your other lists so basically it is a drop down list with the names of the other named ranges
Second you need to use the formula =indirect(a1) in the second (b) column. The second column has a formula that basically says to look at the list in the first column and return that as the named list. Not working!? This was causing me some headaches until I realised that you have to =indirect(a1) as a data validation as well. So the end result of cell b1 is a list with =indirect(a1) as the source
Drag down and/or drag across and the lists will start populating themselves. The real key here is to make sure that the entries in a list are also the same names as the named ranges.
Happy Nerdings!