Cascading Validation Lists

Hi,

I've attached a sample file which outlines roughly what I'm working with.

I will have users filling out cells (e.g. on Input sheet) and want to cascade the drop-down lists based on selections made. The basis for these selections is on the LISTS sheet.

So for Area, if they choose AREA1, then the Main Activity should only have 1,2,3,4,5 as available drop-down options and if they choose 1, then Secondary Task should only have A or B as options in that drop-down and finally Supervisor option should only be XX.

My question is, is there any quick way to create these cascading drop-down lists from the structure I have currently in place on the LISTS sheet. Or am I going to have to rearrange the data on a separate sheet? I will have up to 40 'AREAS' with dependent selections.

Any help greatly appreciated!

Hi Ford,

Thanks for your reply.

Yes, I'm aware of the method you've posted. However, it will mean a lot of work for me in arranging my data to work in that way. I guess I'm looking for a solution that won't require a lot of changes to my current LISTS sheet layout but I'm thinking it probably doesn't exist!

Thanks Again.

Ford,

Would you mind looking at the attached?

I've started to lay out the 'plan' for how I'll approach the validation. I hope you can follow the logic! So on the LISTS sheet, I've set up the various Named Ranges and the INDIRECT formulae on the Input sheet.

In this sample, I have 2 'Areas' but in reality there will be about 40 or 50. Really, I just want to make sure I'm on the right track before I go ahead with the bulk of the data.

Thank You.

CascadingListsEx.xlsx

Wow, that's brilliant! So much more efficent. Brilliant trick Re quickly naming ranges, never knew it existed!

Thank you so much.

There's one more thing I'd like to ask.

For the Supervisor, based on the sample data, the VLOOKUP works perfectly. However, there is one Area where there isn't one specific
supervisor per Secondary Task. For this one area, the user has to be able to select from a list of possible supervisors. I've set up a
new 'Area3' to simulate it. So for Area 3, if a user picks any Main Activity/Secondary Task combo, they will then select the Supervisor from the list of all available Supervisors in Area3.

Is there any easy way to incorporate this?

Thanks again for your help...

Ya, the VLOOKUP would have been perfect only for that one Area.

I'm just going to have to go with the same approach for the Supervisor but it will be perfect.

Thank you so much for your help. Your suggested approach will save so much in development and will be so much easier to update and explain to others!

Many Thanks Again.