1. You are not going to sue me it any thing goes wrong
2. You have a backup copy of the file
This is how I would approach
1. On a new sheet (let say sheet2) in Cells A1, B1, C1 and D1 enter values Mill, Comp, Loads, Blending respectively
2. Put the values of Mill, Comp, Loads and Blending under its respective column heading (make sure no blank line)
3. Go to Formula Tab on menu
4. Select Cells A1:D1
5. Click on define Name
6. Enter the name, lets say "MyMainList" and click Ok
7. Select all the values in col A, starting from A2 and click on name, give it name "Mill"
8. Select all the values in col B, starting from B2 and click on name, give it name "Comp"
9. Select all the values in col C, starting from C2 and click on name, give it name "Loads"
10. Select all the values in col D, starting from D2 and click on name, give it name "Blending "
11. Go to main sheet (lets say sheet1) and select cell A2
12. Go to Data tab on menu and click on Data Validation
13. Click on the drop down titled "Allow" and choose List
14. In Source box write =MyMainList
15. Optionally go to input tab and error tab to enter messages for that
16. Press ALT+F11
17. Double click on "sheet1" in the project explorer
18. Paste code.
Private Sub Worksheet_Change(ByVal Target As Range)
'Target is the cell or a range of cells whose values were changed
' and that triggered the change. Excel itself will handles this part.
' This code is written with assumption that at a time only
' one cell would be changed
' if the changed column is not column A, then we are exiting
If Target.Column <> 1 Then Exit Sub
' we are disabling excel ability to react when the cell or range is
' changed. It will be enabled later on. This prevents cyclic or
' recursive calls
Application.EnableEvents = False
Dim mainsrc As String
Dim subsrc As String
Dim lThisRow As Long
' find which row was modifed that triggered this function to be called
lThisRow = Target.Row
' Check if the value in column A on the row that was changed is ""
If Cells(lThisRow, 1) = "" Then
' since the value in column A is blank, we are deleting any
' validation from Column B on the same row
' goto to a label "exit_sub"
mainsrc = Cells(lThisRow, 1).Value
On Error Resume Next
'attempting to read what is the current validation formula in cell in
' column B of the changed row
subsrc = Cells(lThisRow, 2).Validation.Formula1
On Error GoTo 0
' check if the current validation in the column B is same as one that
' is needed now due to change in the value of column 1
If ("=" & subsrc = mainsrc) Then
' the validation I n column B is for same major category
' no need to reset validation
' since the major category was changed, we reset any selected value
' in column B of the changed row to ""
Cells(lThisRow, 2) = ""
' putting the new validation in B
With Cells(lThisRow, 2).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & mainsrc
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "This is my Input Title"
.ErrorTitle = "Oops Error"
.InputMessage = "Select a Value"
.ErrorMessage = "Not a valid value"
.ShowInput = True
.ShowError = True
' a label to allow for "Goto" statement
' re-enabling the ability for excel to react to events/triggers
Application.EnableEvents = True
19. Post Feedback
20. Send me the money :p