Dropdown dependency (difficult with INDIRECT)

[nailuenlue]

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?

[#2]

@nailuenlue 

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

[#3]

@mathetes 

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

[#4]

@nailuenlue 

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.

[#5]

@mathetes 

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.

[#6 · Accepted solution]

@nailuenlue 

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. 
"

[#7]

@mathetes 

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.

[#8]

@mathetes 

I did slight modifications but your proposal was a big help. Thanks a lot!

[#9]

@nailuenlue 

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.