title: How can I remove blank options from a large dependent drop-down menu in Microsoft Excel?
body: <p>I built a form in Excel which uses data validation to apply drop-down selections. The user selects from one category which creates a dependent list based on the selected category, and this extends to yet another selection. This is set up using a query and VBA as follows: Each dependent drop-down relies on the former by using code like =INDIRECT(A1) where A1 is the first selection and the next list is assigned to a Named Range using the title in A1.</p>
  
  <p>Unfortunately, by the final list selection the number of named ranges seems to have exceeded Excel's capacity (>10k) and so instead I have referred to a fixed range which uses an INDEX MATCH function in each cell to present the options. The problem is that some versions of this final drop-down need 5 options and others need 150, so I have to set the final list to a static 150 cells to accommodate the largest possible data set.</p>
  
  <p>Ignore blank cells does not work in the data validation due to the use of INDIRECT. Is there another way to make this system (or any other system) work for such a large data set?</p>
  
  <p>As a note, I considered forcing the use of buttons [to adjust the named ranges] between selections but I don't believe that is a fit for this user base.</p>