Can I set up a group of range names dynamically using a formula for data validation purposes?
I am looking to generate a drop down list which is context dependent and then use that list to get the correct data from the main table.
The requirement is to generate a drop down list restricted to only the tests for the rows appropriate to the selected animal. I.e. can I generate the drop down lists in cells I3 to I9 in some way that does not require the manual creation of the individual ranges to work?
I am attaching a simple demo of my current method for doing this but this method is very fragile as it is very easy to corrupt the individual range names when adding rows or data points to the main table. It also relies on the the names of the "animals" being formatted as valid variable names which makes the final document harder to read( e.g. having to add lots of underscores into the names).
The full size spreadsheet has around 8 different top level tables each with around 10-15 "animals" and each "animal" has 10-15 possible tests associate with it. Managing all the range names in the name manager is not easy.
Hi Chris,
Using XLOOKUP you can create a dynamic DV list with this formula
=XLOOKUP("mouse",Table1[animal],Table1[test],,,1):XLOOKUP("mouse",Table1[animal],Table1[test],,,-1)
This requires the data in your lookup table to be sorted alphabetically by animal name.
XLOOKUP returns a reference so the first XLOOKUP gives you the first cell (and hence row) where mouse appears. The 2nd XLOOKUP gives you the last cell because it's searching from bottom to top in the list - that's the -1 switch.
You end up with a a reference to only the rows where that particular animal is listed. So as you add/remove rows the reference updates.
I've created the dv list for mouse and left the rest to you - see attached.
Regards
Phil
