Dependent validation - lists and lookup combo

**headexperiment** · 06-13-2011, 09:40 AM

Hello,

Fairly new to the forum so apologies if I haven't formatted my question correctly.

I'd be grateful for help with this:

I have a named range "option_carea"
Its validation is set from a list in another range called "carea"
It has 3 options:
Any
East
West

Next door is "option_cf_team"
If "option_carea" is "Any", I want "option_cf_team"'s validation to come from "cf_teams"
If "option_carea" is "East", I want "option_cf_team"'s validation to come from "cf_teams_east"
If "option_carea" is "West", I want "option_cf_team"'s validation to come from "cf_teams_west"

Next door named "option_woe"
If "option_carea" is "Any", and "option_cf_team" is "Any", I want "option_woe"'s validation to come from "cf_woes"
If "option_carea" is "East", and "option_cf_team" is "Any", I want "option_woe"'s validation to come from "cf_woes_east"
If "option_carea" is "West", and "option_cf_team" is "Any", I want "option_woe"'s validation to come from "cf_woes_west"

(I'm ok this far using Data->validation-> allow list with forumlae in the box.

But if "option_cf_team" is set to anything other than "Any", I want the validation of "option_woe" to come from the lookup table "team_woe_lookup"

I can't seem to find a way to use both lists and lookups, and I'm going round in circles.

Thanks,

HE

**watersev** · 06-13-2011, 10:35 AM

hi, headexperiment, hope this helps

**headexperiment** · 06-13-2011, 10:58 AM

Hi Watersev,

Thanks for looking at it for me - much appreciated. It doesn't seem to have worked..?

Populating Option_Carea and/or option_cf_team seems to prevent the drop-down in from working. Am i doing something wrong?

Thanks,

HE.

**watersev** · 06-13-2011, 11:18 AM

hi, as far as I understood your question:

If "option_carea" is "Any", I want "option_cf_team"'s validation to come from "cf_teams"
If "option_carea" is "East", I want "option_cf_team"'s validation to come from "cf_teams_east"
If "option_carea" is "West", I want "option_cf_team"'s validation to come from "cf_teams_west"

Next door named "option_woe"
If "option_carea" is "Any", and "option_cf_team" is "Any", I want "option_woe"'s validation to come from "cf_woes"
If "option_carea" is "East", and "option_cf_team" is "Any", I want "option_woe"'s validation to come from "cf_woes_east"
If "option_carea" is "West", and "option_cf_team" is "Any", I want "option_woe"'s validation to come from "cf_woes_west"

as far as I can see it works. So if Any is selected in option_cf_team then option_woe come from woes depending on option_carea. What exactly does not work?

**headexperiment** · 06-13-2011, 11:28 AM

Thanks watersev. It works, partly

But if, for example, I set option_carea to "West" and option cf_team to "Avon", the dropdown on option_woe freezes, when it should offer only "Sam".

??

**watersev** · 06-13-2011, 01:03 PM

hi, I beg your pardon I did not read to the end, this should address that.

As pardon me-gesture I've added clearing of option_cf_team and option_cf_woe when Option_Carea is changed.

**headexperiment** · 06-14-2011, 04:54 AM

Perfect. Does the job exactly.

Better go off and do my homework on:

=IF(D25="Any",CHOOSE(MATCH(C25,carea,0),cf_woes,cf_woes_east,cf_woes_west),OFFSET(INDIRECT("Reference!J5"),MATCH($D$25,team_woe_lookup,0),1,1,1))

And:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = "$C$25" Then [d25,f25].ClearContents
    Application.EnableEvents = True
End Sub

Many thanks. HE.