Multiple columns Data Validation in drop down

[McKenzie!]

I want to have a drop down list which is based on another table that can show several fields within that drop down list. The value selected is based on one field. The drop down may show: 721 SERVICE DEPARTMENT, but the value 721 only should be input into the value selected. Can these be dome? Is there any way? Actually I will be exporting this data in into an Access database and also want to keep the field sizes smaller since a separate table can be linked to define 721 that will be equal "SERVICE DEPARTMENT".

[Shreevats]

I would like to let you know that drop down data validation displays Product Id and Product name. In that an event procedure changes the selection to product name. For you requirement I suggest you to visit the official website of Excel in there you will find Sample Excel Files - Excel Templates in that download Zip file of Data validation for drop down. Set it up and it will do the work for you, exactly the way you wanted.

[Villiers]

If you go to the Excel official website from your browser you will find many Excel Templates in that and they are sample ones. You just have look at the DV004 and DV005, Both of them will work fine as per your needs. Set that to display a description and use the vba to enter the equivalent code. I would like to give you an example such as a client select ‘Banking’ from the drop down list and the code ‘Ban’ is entered into the cell.

[Ronchi]

execute every time you add new ProjectId and ProjectName. Just right click the Worksheet Tab, in that select View code and paste the followimg:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
        If Target.Column = 1 Or Target.Column = 2 Then
            UpdateValidationCell
            Target.Select
        End If
End Sub

Sub UpdateValidationCell()
    Set ProjectIdRange = Range(Range("A2"), Range("A2").End(xlDown))
    Set ValidtListRange = Range(Range("C2"), Range("C2").End(xlDown))
    For Each CL In ProjectIdRange.Cells
        CL.Offset(0, 2).NumberFormat = "General"
        CL.Offset(0, 2).Formula = "=" & (CL.Address)
        CL.Offset(0, 2).NumberFormat = "@    " & """" & CL.Offset(0, 1).Value & """"
    Next
    Set ValidtListRange = Range(Range("C2"), Range("C2").End(xlDown))
    Range("G2").Select
    Selection.Validation.Delete
    Selection.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & ValidtListRange.Address
    Columns("C").Hidden = True
End Sub

[Dwarner]

If you still haven’t found any help then ignore you prior code that you have used and use the below code in your actual source. I am sure this will help you. I have edited as per you needs.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column = 1 Or Target.Column = 2 Then
UpdateValidationCell
Target.Select
End If
End Sub

Sub UpdateValidationCell()
Set ProjectIdRange = Range(Range("A2"), Range("A2").End(xlDown))
Set ValidtListRange = Range(Range("C2"), Range("C2").End(xlDown))
For Each CL In ProjectIdRange.Cells
CL.Offset(0, 2).NumberFormat = "General"
CL.Offset(0, 2).Formula = "=" & (CL.Address)
CL.Offset(0, 2).NumberFormat = "@ " & """" & CL.Offset(0, 1).Value & """"
Next
Set ValidtListRange = Range(Range("C2"), Range("C2").End(xlDown))
Columns("C").Hidden = True
End Sub

[Author]

According to me you should use dta validation formula. To make you understand better I will give you an example such as take column A and column B, Data validation for column B changes depending on the value selected from column A. The dta validation formula is as follows:

=IF(A7="Dining Room",DR,IF(A7="Living
Room",LR,IF(A7="Kitchen",KT,IF(A7="Master Bedroom",MBR,IF(A7="Guest Bedroom
1",GB1, IF(A7="Guest Bedroom 2",GB2, IF(A7="Guest Bedroom 3",GB3,IF(A7="Guest
Bedroom 4",GB4,""))))))))

[Steyn]

The below code will help you have 24 different options. Just copy pastes the formula. I have edited as per your requirement so you don’t have to make any changes.

• =CHOOSE(MATCH(A7,{"Dining Room";"Living Room";"Kitchen";"Master
  Bedroom";"Guest Bedroom1";"Guest Bedroom2";"Guest Bedroom3";"Guest
  Bedroom4"},0),DR,LR,KT,MBR,GB1,GB2,GB3,GB4)