Dynamic dropdowns help

[DWNewmac]

Hello.

I've been trying to create a dynamic dropdown that provides different lists depending upon the users selections. I've previously done this with simple two column tables, but what I'm looking to achieve here is on a much bigger scale. I've attempted creating table within tables but when attempting to create a table within a table, the option to create a table is greyed out.

I appreciate that there may be quite a bit of setting up to do here, but if someone if able to provide me some steps, in layman's terms that will help me to achieve the aim, it would be much appreciated.

Requirement

1. The first selection is the client name: Cell M5 on the Input sheet.
2. The second selection is the job type which are in Column E (the job types are the same for both clients)
3. The third selection is an 'item' list in Column G (the items are unique to the client selected in M5)
4. The remaining selections (listed as Detail 1 to 20 in Columns M to AY) and should present a dropdown list from either the 'AbiDropdowns Sheet' tab or the 'CorDropdowns Sheet'. These are therefore dependent on the first, second and third selections.

Example

For example: selection 1 = Abi, selection 2 = install new, the list presented for selection 3 should be install new items on the AbiDropdowns sheet (in this instance the dropdown would refer to column AV). If the user then selected Abi JPS, the remaining selections (Detail 1 to 20) would return a list that refers to Column BB.

I've also enclosed an example spreadsheet, which hopefully helps to clarify the requirement in more detail.

Test.xlsx

Thanks

David

 

[Fluff]

Sounds like you want dependant dropdowns, have a look here.

How to make a dependent (cascading) drop-down list in Excel

See how to create multiple dependent drop down lists in Excel based on the value selected in another dropdown, how to prevent changes in the primary drop-down menu and make dynamic depending dropdowns.

www.ablebits.com

 

[DWNewmac]

Thanks for this, Fluff. That's useful and has helped with a chunk of what I needed.

However, I'm struggling to get it to work a second dropdown, which I think is because the first dropdown is not unique (it's a list of job types, which are the same for each client).

The first dropdown E9 gives the job types using data validation:

IF($M$5="Abi",JobActionAbi,IF($M$5="Cor",JobActionCor,""))

This works as required.
So what I'm wanting is that the second dropdown

=INDIRECT(SUBSTITUTE(E9," ",""))

will select from one of two (at this stage but could be more) tables that reference E9 that will return items specific to the client.

Because the first dropdown isn't unique, I'm looking for an approach that will allow me to achieve the above but without having unique names for job types per client.

Is there a way to do this, that I'm missing?

Many thanks

David

 

[Fluff]

Unfortunately, I think that you will need unique named ranges.

 

[DWNewmac]

Thanks Fluff - that's a shame.

You don't have a piece of code that will change the last character in a cell to white do you?

Cheers

David

 

[Fluff]

Yes, but as that's a new question, it needs a new thread. Thanks

 

[DWNewmac]

Apologies - will add one now.

Thanks

David

 

[Fluff]