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 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.
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 did slight modifications but your proposal was a big help. Thanks a lot!
I fully expected you to have to make some modifications. I was only trying (without knowing the full picture) to give an idea of how the task could be accomplished. Glad it helped. And I'm sure you have noticed that INDIRECT did make a showing.......in the final data validation formula.
Thanks a lot for putting the time in to provide help 🙂 Really appreciate it!
My problem is really that the roles in the raw data has to come from a named range as, there are potentially 10-2 roles and I don't want people to add entries based on free text but much more pick from a list of allowed roles for each line.