Excel: How to dynamically create a dropdown list based on another dropdown's selection with defined limits?
I'm refactoring my project and I am trying to create a dependent dropdown list in Excel where the options in the second dropdown vary based on the first dropdown's selection. Furthermore, I want to set specific limits for the selection of the second dropdown depending on the choice made in the first. I have been using Excel 365 and have implemented the following steps: 1. I created a named range for each category in the first dropdown. For instance: `Fruits` and `Vegetables`. 2. In another worksheet, I defined the valid options for each named range. 3. For the first dropdown, I used Data Validation with the source set to `=Categories` where `Categories` is the range containing `Fruits` and `Vegetables`. 4. For the second dropdown, I attempted to use `=INDIRECT(A1)` where `A1` is the cell containing the first dropdown selection. However, I am getting an behavior saying "The source currently evaluates to an behavior". I tried using the formula `=IF(A1="Fruits", Fruits, IF(A1="Vegetables", Vegetables, ""))` but I still received the same behavior. Additionally, I want to implement a scenario where if the selected value from the first dropdown is `Fruits`, then the second dropdown should only allow `2` selections at most, and similarly for `Vegetables`, it should allow `3` selections. I am not sure how to enforce the selection limits alongside the dependent dropdown functionality. Any suggestions on how to resolve this scenario? Here's a sample of what my named ranges look like: `Fruits` = Apple, Banana, Cherry; `Vegetables` = Carrot, Spinach, Broccoli. This is part of a larger CLI tool I'm building. What am I doing wrong? Any examples would be super helpful.