Results 1 to 7 of 7

Thread: Multiple columns Data Validation in drop down

  1. #1
    Join Date
    Jun 2011
    Posts
    98

    Multiple columns Data Validation in drop down

    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".

  2. #2
    Join Date
    May 2009
    Posts
    527

    Re: Multiple columns Data Validation in drop down

    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.

  3. #3
    Join Date
    Apr 2009
    Posts
    491

    Re: Multiple columns Data Validation in drop down

    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.

  4. #4
    Join Date
    May 2009
    Posts
    542

    Re: Multiple columns Data Validation in drop down

    execute every time you add new ProjectId and ProjectName. Just right click the Worksheet Tab, in that select View code and paste the followimg:
    Code:
    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

  5. #5
    Join Date
    Apr 2009
    Posts
    570

    Re: Multiple columns Data Validation in drop down

    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.
    Code:
    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

  6. #6
    Join Date
    May 2009
    Posts
    529

    Re: Multiple columns Data Validation in drop down

    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:

    Code:
    =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,""))))))))

  7. #7
    Join Date
    May 2009
    Posts
    640

    Re: Multiple columns Data Validation in drop down

    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)

Similar Threads

  1. how to match data between 2 columns in excel
    By dankar in forum Windows Software
    Replies: 1
    Last Post: 25-04-2013, 11:16 AM
  2. [VBS] Change data in columns in CSV
    By Conus in forum Software Development
    Replies: 1
    Last Post: 30-04-2012, 11:18 PM
  3. Replies: 2
    Last Post: 23-02-2012, 10:54 AM
  4. How to sort multiple columns in Excel Pivot Table
    By Nicoloid in forum MS Office Support
    Replies: 2
    Last Post: 17-02-2012, 07:01 PM
  5. MS EXCEL: Counting based on multiple columns
    By Revati in forum Windows Software
    Replies: 6
    Last Post: 06-11-2010, 11:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •