Conditional drop-down list

[Lebene]

Hi all,
Vers office 365 (with xlookup)

I am already going to implement the formula from this thread Drop Down lists but I am struggling on some mechanics of my drop-down list as I have many conditions.

I want the drop-down list journey to be: Internal/External? -> Provider name -> Target audience -> Virtual or In-person? = List of workshops based on the former criteria.

E.G.

Provider type (finite)	Provider name (infinite)	Target Audience (finite)	Delivery (finite)	Workshop name
External	Knowledge 4 U Ltd	Managers	Virtual	*List of virtual manager workshops provided by Knowledge 4 U*

If that is not possible, I am happy to be able to at least drill-down to: Provider type, name, and target audience so that it shows the full list of what the provider offers for a certain target audience. I just need to ensure it selects the right one because each workshop has its own code so that potential duplicate names aren't an issue. I add extra buffering by putting '(virtual)' at the end of all relevant workshop names.

Thank you for any help or advice.

More optional context:
What I'm doing currently
Currently, I use =INDIRECT() and the provider is the deciding factor for my workshop list, but the issue arises as soon as I get to the split between virtual and in-person events - the label needs to match the drop-down list options that the indirect is referencing, so I can only use 'Internal' and 'In-person' once to make my list of workshops.

My main issue is that I now want to split my list by target audience (there is only 3) and not provider (infinite) in order to future-proof it. If I keep doing it by the provider, my drop-down list sheet and labels will be way out of control as time goes on and more difficult to upkeep and avoid people error.

 

[Lebene]

I've been mulling around and I guess if I can make the searchable drop-down list be conditional based on my criteria that could work? I just can't figure out how to nest a formula like that.

 

[Lebene]

I found this solution which addresses my issue of having selections that aren't unique (within Delivery and Target Audience) but it only goes to 3rd dependents. Hopefully, as I have two fields that are not unique opposed to one, it still works. As I wanted to future-proof my plans for the Provider field, it wouldn't work HOWEVER, I will just have to bite the bullet for now and start my dependencies starting from Provider, rather than Provider type.

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

 

[Lebene]

Solution

After sound boarding off a kind colleague, I figured it out with the link above.

So now I have a dependent list that works off the criteria of Target audience, delivery and provider.

The last formula in the 'Workshop name' field just had to be amended to add the fourth level e.g.: =INDIRECT(SUBSTITUTE($B2&$C2&$D2," "," "&" "))

If anyone stumbles through this thread when searching for the same solution and has questions, feel free to ask me!

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com