Conditional Validation List

[SLARRA]

I'm sure this has been asked many times, but I can't seem to turn up an applicable solution...

I have a workbook with tables on separate worksheets:

• Sheet named "CompanyData" has a (formally designated) table named "Companies" that includes a data field named "CompanyID". There is one record per company, each with a unique "CompanyID".
• Sheet named "SalesRepData" has a (formal) table named "SalesReps" that includes data fields named "CompanyID" and "SRepID". For each Sales Rep's record in this table, I pick the his/her "CompanyID" (using Excel's built-in data validation tool, referenced to a list whose source is the "CompanyID" field in the "Companies" table <-- this I can do already), and then create and assign a unique "SRepID". This table is not sorted, and there can be multiple Sales Reps associated with any given Company. For example, there may be 5 records (SRepIDs: AJones01, BSmith01, JDavis01, BSmith02, and MThomas01) that collectively document the Sales Reps who all work for a particular company (CompanyID: 123-AcmeInc)

On a third sheet, which will have a table of transactions, I want to create new records by first picking a CompanyID (again, via validation list tied to the "CompanyID" field in the "Companies" table <-- this I can do already) and then associate the transaction with that Company's Sales Rep who initiated it (<-- this is where I need some help). That is, I want to pick a SRepID from a validation list that is sourced from the SRepID field on the SalesReps table. But, I want that validation pick list to be filtered to only show the Sales Reps who work for the chosen Company. For example, if the transaction record is associated with the 123-AcmeInc company, I want the pick list domain for that table's SRepID field to be constrained to only list AJones01, BSmith01, JDavis01, BSmith02, and MThomas01.

To enable that conditional (filtered, based on CompanyID) validation list for transaction records, I think there are at least 2 obstacles. First, the sources are "living" tables, with new Companies and new Sales Reps being added daily to their respective tables. Secondly, the SalesReps table is not sorted, so a given company's Sales Reps' records are not grouped into a contiguous range. Thus, I presume, deriving the validation list source will involved using array formulas, named ranges with the INDIRECT function, and - possibly - MATCH and OFFSET techniques, but I am unable to solve this myself.

Thanks in advance!

SDL

 

[Fluff]

If you're interested in a VBA approach, how about

Option Explicit
Private Dic As Object

Private Sub Worksheet_Activate()

   Dim Cl As Range
   Dim Lst1 As String
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   With Sheets("SalesRepData").ListObjects("SalesReps").DataBodyRange
      For Each Cl In .Range("SalesReps[CompanyID]")
         If Not Dic.exists(Cl.Value) Then
            Dic.Add Cl.Value, CreateObject("scripting.dictionary")
            Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
         ElseIf Not Dic(Cl.Value).exists(Cl.Offset(, 1).Value) Then
            Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
         End If
      Next Cl
   End With
   Lst1 = Join(Dic.keys, ",")
   With Range("J2").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Lst1
   End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Lst2 As String

   If Target.CountLarge > 1 Then Exit Sub
   If Not Target = Range("J2") Then Exit Sub
   Target.Offset(, 1).ClearContents
   Lst2 = Join(Dic(Target.Value).keys, ",")
   With Range("K2").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Lst2
   End With

End Sub

This code needs to go in the sheet module, for the "Third sheet".
It uses the "CompanyID" column on sheets "SalesRepdata" for the first data validation list & the next column for the SRepID list.
It currebtly puts the data validation dropdowns in J2 & K2

 

[SLARRA]

Thanks, Fluff, for the help. It's getting me closer, but not quite there....

I'm not a VBA person, so the code is mostly unintelligible to me.

When I pasted it into the module and ran it, I do get validation pick lists in "Third Sheet" cells J2 & K2. But, the list in J2 is not the CompanyID values, but rather the SRepID values. And the (nicely filtered, tho pointed at the wrong source) list in K2 is the Sales Rep's last names, which happens to be the field immediately to the right of the SRepID field in the SalesReps table on the SalesRepData sheet. So... I think the offsets are pointing 1 column too far to the right. To be clear, I want J2 to show a list of CompanyID values and K2 to show the list of SRepID values that are associated with whatever company I pick in J2.

Once we get this code tuned up a bit, how then would I enact that validation on all the table entries on "Third sheet", in a (formal) table named "Transactions" that has fields for CompanyID and SRepID? That is, how does the VBA need to change so that the validation isn't enacted in cells J2 & K2, but for all Transaction table entries in the CompanyID and SRepID fields?

Thanks for the efforts so far (and I hope you'll give me a little more help)!

SDL

 

[Fluff]

In the SalesRep table do you have a column containing the CompanyID values & if so what is that column called?

 

[SLARRA]

Yes. In the SalesReps table (on the sheet named "SalesRepData"), there is a field labeled/named CompanyID, (whose header is in cell C6). The field labeled/named SRepID is immediately adjacent to the right (with the header in cell D6).

Thanks!

SDL

 

[SLARRA]

Edited to include the code I'm using

Yes. In the SalesReps table (on the sheet named "SalesRepData"), there is a field labeled/named CompanyID, (whose header is in cell C6). The field labeled/named SRepID is immediately adjacent to the right (with the header in cell D6).

I don't think it matters here, but note that the SalesReps table's name is plural, while you referred to it in the singular in your previous reply.

I'll post the code from my spreadsheet below. I pasted your original code in the module for "Sheet 4", which I named "Third Sheet" to match your original post. I can't see any overt problems, but I don't understand VBA. Tho I intended to simply cut-n-paste your suggested code, perhaps I've inadvertently changed something.

Option Explicit
Private Dic As Object

Private Sub Worksheet_Activate()

   Dim Cl As Range
   Dim Lst1 As String
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   With Sheets("SalesRepData").ListObjects("SalesReps").DataBodyRange
      For Each Cl In .Range("SalesReps[CompanyID]")
         If Not Dic.exists(Cl.Value) Then
            Dic.Add Cl.Value, CreateObject("scripting.dictionary")
            Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
         ElseIf Not Dic(Cl.Value).exists(Cl.Offset(, 1).Value) Then
            Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
         End If
      Next Cl
   End With
   Lst1 = Join(Dic.keys, ",")
   With Range("J2").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Lst1
   End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Lst2 As String

   If Target.CountLarge > 1 Then Exit Sub
   If Not Target = Range("J2") Then Exit Sub
   Target.Offset(, 1).ClearContents
   Lst2 = Join(Dic(Target.Value).keys, ",")
   With Range("K2").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Lst2
   End With

End Sub

Thanks!

SDL

 

[Fluff]

Ok, try this change

   With Sheets("SalesRepData")
      For Each Cl In .Range("SalesReps[CompanyID]")

 

[SLARRA]

That change fixed the problem - thanks! With this correction, the full list of CompanyID values appears in the validation pick list in cell J2, and - depending on the company selected in J2- a filtered list of that company's associated SRepID values appears in the validation pick list for cell K2.

Now, what adjustment to that VBA will allow the validation to occur in all cells in the table named "Transactions" (on sheet named - for now - "Third sheet"), for the data fields named CompanyID (header in C6) and SRepID (header in D6)? I hope you can help me get over this last major hurdle...

At the risk of asking too much, I'm also concerned a minor problem may arise when someone adds (or, I suppose) deletes a company or sales rep from their respective tables. If the user then wants to re-edit a transaction, will this VBA code automatically re-populate the validation pick lists to reflect the current data in the SalesReps table?

Thanks for all the help!

SDL

 

[Fluff]

This should put the DV into the table

Option Explicit
Private Dic As Object

Private Sub Worksheet_Activate()

   Dim Cl As Range
   Dim Lst1 As String

   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   With Sheets("SalesRepData")
      For Each Cl In .Range("SalesReps[CompanyID]")
         If Not Dic.exists(Cl.Value) Then
            Dic.Add Cl.Value, CreateObject("scripting.dictionary")
            Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
         ElseIf Not Dic(Cl.Value).exists(Cl.Offset(, 1).Value) Then
            Dic(Cl.Value).Add Cl.Offset(, 1).Value, Nothing
         End If
      Next Cl
   End With

   Lst1 = Join(Dic.keys, ",")
Application.EnableEvents = False
   With Range("Transactions[CompanyID]").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Lst1
   End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Lst2 As String

   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("Transactions[CompanyID]")) Is Nothing Then Exit Sub
Application.EnableEvents = False

   Target.Offset(, 1).ClearContents
   Lst2 = Join(Dic(Target.Value).keys, ",")
   With Target.Offset(, 1).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Lst2
   End With
Application.EnableEvents = True
End Sub

Every time you activate the "Third sheet" the DV lists will be recaclulated

 

[SLARRA]

Nailed it! Thanks!

SDL