Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Multiple columns Data Validation in drop down

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 15-07-2011
Member
 
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".

Reply With Quote
  #2  
Old 15-07-2011
Member
 
Join Date: May 2009
Posts: 523
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.
Reply With Quote
  #3  
Old 15-07-2011
Member
 
Join Date: Apr 2009
Posts: 481
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.
Reply With Quote
  #4  
Old 15-07-2011
Member
 
Join Date: May 2009
Posts: 531
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
Reply With Quote
  #5  
Old 15-07-2011
Member
 
Join Date: Apr 2009
Posts: 566
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
Reply With Quote
  #6  
Old 15-07-2011
Member
 
Join Date: May 2009
Posts: 522
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,""))))))))
Reply With Quote
  #7  
Old 16-07-2011
Member
 
Join Date: May 2009
Posts: 614
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)
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Multiple columns Data Validation in drop down"
Thread Thread Starter Forum Replies Last Post
how to match data between 2 columns in excel dankar Windows Software 1 25-04-2013 11:16 AM
[VBS] Change data in columns in CSV Conus Software Development 1 30-04-2012 11:18 PM
Is there a feature like Always show in Data Validation Drop Down List CeLeStiA MS Office Support 2 23-02-2012 10:54 AM
How to sort multiple columns in Excel Pivot Table Nicoloid MS Office Support 2 17-02-2012 07:01 PM
MS EXCEL: Counting based on multiple columns Revati Windows Software 6 06-11-2010 11:14 AM


All times are GMT +5.5. The time now is 01:46 PM.