Forum Discussion
Dropdown dependency (difficult with INDIRECT)
OK... Play around with this. I eliminated your "utility" sheet as redundant. The "raw data" sheet contains all the tables you need, unless you add new roles, in which case you'll have to expand a few references to them. But this shows the basic structure.
On the Positions sheet you should Hide Column C, which is just there as a Helper column, to provide the address for the data validation for the entries (variable entries) that go into Column B based on what Role is picked in Column D.
If it were mine, I'd reverse the sequence, so you pick Role on the left, and then select the Position to the right.... just makes more sense in a left to right world. But the same data validation rules would apply.
"
I think this is what you're looking for. https://exceljet.net/dependent-dropdown-lists
The discussion there is about using Dynamic Array along with INDIRECT to come up with changing subordinate levels of data validation, a set of data validation values that vary with the primary selection.
You might also benefit from this YouTube video on Dynamic Arrays: https://www.youtube.com/watch?v=9I9DtFOVPIg
The indirect concept doesnt work as it assumes a flat data structure as a source for the dependency. So you would have the data sheet where everyone would fill the roles and positions without the drop downs (which would then result in a data mess or hell lot of validation formulas without lists).
I really need to populate the roles drop down in the second sheet with the selected entries in the roles column (which is a named range) in the data sheet and populate only the positions in the second sheet, which have been picked in the data sheet
I think we may be talking past each other. Here's a sample I created just to illustrate dependency of data validation tables. If that's not what you're looking for, then I have been misunderstanding what that is. However, I do see that this does not use INDIRECT. It does use several of the newly available dynamic array functions.
Try adding names to the basic table here....you'll see that the sets of data for the primary and secondary data validation entries automatically accommodate the new names, first and./or last.
I made a sample Excel file to show the usecase with descriptions. Please check the raw_data & positions sheet for the explanations and the util sheet for the data used in the named range.
