Forum Discussion
Data Validation - Restriction Not Working
Hi Amy
I have split the list into 5 lists to make it easier to work with - not particularly elegant but so long as you don't create to many genres it seems to work ok.
Hope this is what you wanted it.
Obviously you and/or your brother are in the entertainment business - my daughter started a job there as well - quite difficult in these trying times.
all the best.
Peter
Let me suggest a different way to approach it.
You're obviously (because you know how to use OFFSET and named ranges) more than a beginner with Excel. So here's a different way to approach it.
- Set up your secondary Data Validation tables as distinct tables, and give them names.
- Add a "helper column" between your two data validation columns. In that, use a conditional =IF formula to select the appropriate table name for the sub-category.
- Refer to that sub-category name in the Data Validation formula, using =INDIRECT(Whatever_the_AdjacentCell_is)
mathetes Thank you for your reply. I didn't actually create the data validation, it was my brother and it took him a long time to figure out and he says your suggestion is way out of his skill set unfortunately (or he's being lazy IDK 🙂 ). Is there a way of just getting the 2nd column to do what it's meant to do?
AmyVick87 There is no error, though the message comes up when you enter the DV rule but have not yet filled in the main genre. Ignore the message and just start using the schedule as you intended.
But I agree with mathetes that you can do it a lot easier with a few named ranges and INDIRECT.
Just make sure that the ranges for the sub-genres are called exactly like the name of the main category.
Have made some changes to your file (attached). The green coloured cells have DV with INDIRECT.
Nice solution. Still INDIRECT but more direct than mine; no need for the helper column. I'll remember that.
Riny_van_Eekelen thank you for your help but I will use what peteryac60 sent me. Thanks again
AmyVick87 You're welcome. Personally, I feel that the INDIRECT option is easier and more flexible. Your choice.
