Conditional drop-down list

Lebene

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
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
ExternalKnowledge 4 U LtdManagersVirtual*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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Lebene

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
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

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
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.

 

Lebene

New Member
Joined
Sep 20, 2019
Messages
40
Platform
  1. Windows
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!

 
Last edited by a moderator:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,122,930
Messages
5,598,925
Members
414,268
Latest member
bluebandersnatch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top