Solved Data Validation Intersect vba

February 27, 2015 at 03:24:21
Specs: Windows XP, 2 GHz / 958 MB

i have a template where in 2 columns are related to each other. I want to automatically create a validation list for the other column based on what I select from the other column.

Column C is for Category and Column E is for Basket

If I click column C validation list should populate in column E based on what I select in column C
If I click column E validation list should pouplate in column C based on what I select in column E

Sample data:

Category Basket
SOLID 1, 2, 3, 4
LIQUID 9, 8, 7, 6
GAS 21, 45, 67

So if I select SOLID in Category, validation list for Basket should be 1, 2, 3, 4
if I select 1 in Basket, category should be automatically show as SOLID.

This should apply to each row on that column.

Thank you

See More: Data Validation Intersect vba

Report •

February 27, 2015 at 04:29:02
✔ Best Answer
You don't need VBA for this. You can use a technique referred to as Dependent Data Validation lists.

However, your "reverse" question doesn't seem to make sense. If you select Solid, the Basket list will contain 1, 2, 3, 4. Selecting 1 doesn't need to change the Category list because it already displays Solid. If it didn't, you wouldn't have a 1 to choose.

The only way for changes to the Basket list to impact the Category list is if you had some method of "resetting" the Basket list so that it contained every choice for all of the Categories. Is that your plan?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 1, 2015 at 18:21:17
Hi DerbyDad03,

Thank you for your response.

The validation makes sense. As to your last part of your answer..Yes would it be possible to refresh the list?

Thank you.

Report •

March 2, 2015 at 09:04:17
I don't know if this is what you are looking for, so let me know....

First I will tell you how I set up my worksheet, and then I will tell you how it works.

I started with the following set up, with A2 and B2 containing Data Validation drop downs as explained below.

	A         B         C       D       E      F
1   Category    Basket    Solid   Liquid   Gas   Reset
2    Liquid	  7         1       9      21
3                           2       8      45
4                           3       7      67
5                           4       6	   63

There are 5 Named Ranges:

Categories:   C1:F1
Solid:        C2:C5
Liquid:       D2:D5
Gas:          E2:E5
Reset:        C2:E5

A2 contains a Data Validation drop down that refers to the named range Categories so that it will always contain these choices:


In B2 I used the Dependent Data Validation Technique from the Contextures site to create Dependent Drop Downs so that B2 will contain the data specified by the value displayed in A2, based on the named ranges specified above.

I then placed this Worksheet_Change macro in the Sheet module for the sheet where this data exists:

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Basket drop down
    If Target.Address = "$B$2" Then
'If True, Determine if Category drop down displays Reset
       If Range("$A$2") = "Reset" Then
'If True, find column heading of current value in
'Basket drop down and place column heading in A2
          With Range("Reset")
            Set c = .Find(Target, lookat:=xlWhole)
              Range("$A$2") = Cells(1, c.Column)
          End With
       End If
    End If
End Sub

How it Works:

1 - The Dependent Data Validation technique will populate the Basket drop down in B2 based on the data in the individual Named Ranges. When Reset is chosen from the drop down in A2, the Basket drop down will be populated with all of the choices in C2:E5.

2 - Each time the Worksheet is changed, the Worksheet_Change macro will fire. If the change was made to the Basket drop Down in B2, the code will check to see if A2 contains Reset. If it does, it will search the named range Reset for the value currently displayed in the Basket drop down. It will then place the column heading for that value in A2. e.g If A2 contains Reset and 67 is chosen from the Basket drop down, the code will put Gas in A2.

3 - The placement of the Column heading in A2 will then trigger the Dependent Validation feature to repopulate the Basket drop down with the appropriate choices for the Category displayed in A2. e.g If the code places Gas in A2, then the Basket drop down will now contain the choices from the named range Gas.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

March 2, 2015 at 19:29:48

Thank you so much DerbyDad03! :)

Report •

Ask Question