title: Data validation without named ranges
body: <p>I have the following example list:</p>
  
  <p><img src="https://i.stack.imgur.com/KQ4f3.png" alt="list"></p>
  
  <p><strong>Note:</strong> In my real list I have around 200 options and 400 suboptions</p>
  
  <p>And I would like to have 2 dropdownlists to select any option and it's suboptions:</p>
  
  <p>For options, I used data validation - list with range =$A$8:$A$12</p>
  
  <p><img src="https://i.stack.imgur.com/TOLEg.png" alt="options"></p>
  
  <p>And for suboptions I tried the following:</p>
  
  <p><strong>Named ranges</strong></p>
  
  <p>It works but it needs a lot of manual work to maintain as the suboptions list is updated kind of frequently and AFAIK I would need to create and maintain many named ranges as many options I have.</p>
  
  <p>Example</p>
  
  <pre><code>Named Range:     _ABC05
  Refers To:       =Sheet1!$D$9:$D$10
  Data validation: = INDIRECT(CONCATENATE("_";SUBSTITUTE(A2;"-";"")))
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/gOgLf.png" alt="enter image description here"></p>
  
  <p>Again, this works but I am trying to avoid to maintain 200 named ranges.</p>
  
  <p>Any solution without using named ranges or vba?</p>