Nov 21 2021 03:56 AM
Hi,
I have some lists and each of these lists has sub-lists. I am trying to create conditional data validated cells i.e. the validated list on the second cell should change depending on the value selected in the first cell.
I realised that the easiest way to do this is
using vlookup on the first value with the data-list as a range
Finally, using the result of vlookup (the named range) though INDIRECT in the data validation window.
Everything works fine but the problem is, it is really cumbersome to create a named range for all the sub-lists especially when there are hundreds of them. Since I already have the name and the range listed on the sheet, I was wondering if there is a way to create a named range automatically from this data instead of entering it manually in the name manager.
Nov 21 2021 04:50 AM - edited Nov 21 2021 05:10 AM
@Gekkouga I think you can do this without named ranges. The validation formula could look like this:
=INDIRECT(INDEX(references,MATCH(selection,list,0)))
where "references" is the array with applicable cell references for the sublists, "selection" is the list for which the sublist should be in the dropdown and "list" is the array with all the lists.
I didn't go though the trouble of replicating your schedule, but I did make a small example that demonstrates the principle. Perhaps it helps you to implement it in your own sheet.
Edit: Attached a new file.