Two Validation lists in Excel - Default value in second please

[Welshbird]

So, I'm not a VBA girlie really - Have created little bits here and there, but...

I have two drop-down lists in an Excel spreadsheet in which the second list is dependant on the first.

So, I have used the data validation option for a named range and then used list: Indirect and the range in the second box.

So far, so good.

However, if the first drop down is altered, I want the second to default to the first item in the appropriate list, rather than having to wait for the use to click onto the second validation list.

How do I achieve that then?


Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

hi,



Right-click the SHEET TAB to expose the Code WIndow for the sheet containing your Data > Validation Drop Downs.

Above the Code Window are TWO drop downs.

In the Left-Hand Drop Down select Worksheet

In the Right-Hand Drop Down select Change

This is the Worksheet Change event and you want to

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, range("YourFirstDDCell")) Is Nothing Then
        range("YourSecondDDCell") = Sheets("YourSecondLIST").Range("RefOfSecondList")(1)
    End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Welshbird]

OK - So YourFirstDDCell and YourSecondDDCell are obvious to me.

YouSecondLIST and RefOfSecondList are passing me by I'm afraid.

Can you point me as to what to put in here?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

YourSecondLIST is the SHEET NAME of the sheet where your second list resides.

RefOfSecondList is the Range Reference of the second list.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Here's how i coded mine...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [SelectedCC]) Is Nothing Then
        MG_List
        [SelectedMG] = Sheets("MG List").Range("MACH_GRP")(1)
    End If
End Sub

where
[tt]
SelectedCC is the NAMED RANGE of my FIRST DV Cell.

MG_List is a procedure (macro) that runs a query based on the SelectedCC

SelectedMG is the NAMED RANGE of my SECOND DV Cell.

Sheets("MG List").Range("MACH_GRP") is the full reference where my SECOND list resides.
[/tt]
As you see, I use NAMED RANGES exclusively.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Welshbird]

OK - I've tried to add this is (being a VBA nutter) and I don't seem to see that it works at all.

One thing I just don't understand, is that the named range for the second drop down could be one of two - based on the decision taken in the first drop down.

What am I missing in my understanding here?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

the named range for the second drop down could be one of two

TILT! Much more complicated than it need to be!

ONE table, ONE range. That way, regardless if you are dealing with two subsets or two hundred subsets, it will work seamlessly.

Please post a sample table of the data you are dealing with, related to the first list.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Welshbird]

I don't understand how that works then.

I have data like the following:

Region Territory
LO01 TE1
LO02 TE2
LO03 TE3

Region and territory and not linked. The data I use to sum for the aggregation has region in one colum, and territory in another.

The first drop down decides whether I want to see things by Region or by territory, and the second selected either ALL (and I show them all individually) or one specifically.

Does that help clarify?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

You make an initial selection of either Region or Territory, yes?

Then this is your control table. NORMALIZED data.
[tt]
Sel SelValue or lable per your needs
Region LO01
Region LO02
Region LO03
Territory TE1
Territory TE2
Territory TE3
[/tt]
Then, based on your FIRST selection your SECOND DV List could either be generated via MS Query or using a FORMULA like...
[tt]
=OFFSET(Sel,MATCH(FirstSelection,Sel,0)-1,1,COUNTIF(Sel,FirstSelection),1)
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Welshbird]

I see what you are saying, but the data appears in a format from the supplier which isn't like that unfortunately.

I think I'm beginning to think that this doesn't have to be quite as dynamic as I thought - maybe I'll have two sheets - one at Region level, and one at Territory. That way the use can still change products and time periods etc, but the whole thing gets much faster and more manageable.

I really appreciate all of your help though.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

Since you will only, and forever, only have two lists, Region & Territory, then based on the selection in the FIRST DV list, the SECOND DV List can be simply...
[tt]
=INDIRECT(YourFirstDDCell)
[/tt]
and the code to populate the first value in the DV2

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, range("YourFirstDDCell")) Is Nothing Then
        range("YourSecondDDCell") = Sheets("YourSecondLIST").Range(range("YourFirstDDCell"))(1)
    End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Just another point related to your your previous post...

I have data like the following:

Region Territory
LO01 TE1
LO02 TE2
LO03 TE3

Region and territory and not linked.

When you post two SEPARATE lists in that manner, it strongly implies that they ARE linked.

Two un-linked lists, ought not to be structured in the same sheet in adjacent cells, as your post implies they are. It is a practice that can lead to problems.

Each list/table ought to be isolated from all other data in a sheet, by at least one empty column. Other data ought not to reside BELOW any list/table. Except for simple lists, I place each list/table in separate sheets.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Welshbird]

They do appear in the data next to each other, as that particular line of data could appear in Region1, and also in Territory1, but this is simply because Region1 and Territory1 can overlap. Territories do not fit completely into regions.

We do have a very interesting geographical structure here!

I've also just discovered that there is a requirement to add some products together rather than displaying them all individually.

Hmm. Back to some head scratching I think!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen