Forum Discussion
Dropdown dependency (difficult with INDIRECT)
Hi 🙂
I have a problem, which is bugging me for some hours now. I basically have a data sheet, which has the columns:
- Role
- examples: Architect, Engineer etc.
- Position
- examples: Solution Architect XYZ, Cloud Engineer ABC
This data sheet will be populated with multiple positions, which are of a particular role type. The entries for Role and Position come from named ranges.
Now I have another sheet, where I maintain the candidates, which applied for a position. So, I basically want to add entries to that sheet, which now has the columns Candidate, Role, Position. The role dropdown should only include entries, which exist in the data sheet (so not just the total of the named range for role but the ones that have been picked in the data sheet) with the corresponding positions (so, again a subset of the named range, which exists in the data sheet).
Do you have an idea on how to solve this?
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.
"
8 Replies
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.
