|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:
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
Set c = .Find(Target, lookat:=xlWhole)
Range("$A$2") = Cells(1, c.Column)
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.