Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It is possible. One way to approach this is to call a sub that lists you the relevant agents whenever the Target in your Worksheet_Change intersects with validation cell1 or cell2 -> the validation changed.</p> <p>Then you would run a sub with 3 paramenters srcRange, validationColumn and validationValue that goes throgh each row of srcRange and checks if the cell on position rownumber, validationColumn is equal to validationValue if so it outputs the agent and sets outputrow + 1</p> <p>Put this VBA in your Sheet:</p> <pre><code>Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim watchRange As Range Dim validationValue As Range Dim validationColumn As Integer Set watchRange = Me.Range("H1, I1") ' Validation Cells ' If Not Intersect(Target, watchRange) Is Nothing Then Set validationValue = Me.Range("I1") validationColumn = 0 With Me.Range("H1") If (.value = "X") Then validationColumn = 2 If (.value = "Y") Then validationColumn = 3 If (.value = "Z") Then validationColumn = 4 End With listAgents Me.Range("B3:E6"), validationColumn, validationValue End If End Sub Private Sub listAgents(ByRef srcRange As Range, ByVal validationColumn As Integer, ByRef validationValue As Range) Dim outputStart As Range Dim row As Range Dim i As Long Set outputStart = Me.Range("H3") outputStart.CurrentRegion.Clear If validationColumn = 0 Then MsgBox "Can't find Validation Column" Exit Sub End If i = 0 For Each row In srcRange.Rows If (row.Cells(1, validationColumn) = validationValue) Then outputStart(1 + i, 1) = row.Cells(1, 1) i = i + 1 End If Next row End Sub </code></pre> <p>I tested it on your example and it worked.</p>
    singulars
    1. This table or related slice is empty.
    1. POPopulate list via a bi-Condition
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmarg
    1. USmarg
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPopulate list via a bi-Condition
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou are right, this does work. And it works nicely. I checked the code and tried to pull the data using the listAgents Me.Range("B3:E6") but from a different sheet e.g listAgents Me.Range("sheet2!B3:E6") will that work because it doesnt seem to work on my end.
      singulars
      1. PO
      1. USOleg Tarassov
    2. COThe Me refers to the sheet the code is in. So You can call listAgents Sheet2.Range("B3:E6") | listAgents Sheets("Sheet2").Range("B3:E6") or listAgents Sheets(2).Range("B3:E6")
      singulars
      1. PO
      1. USmarg
    3. CONIce, ill try that! thanks Marg
      singulars
      1. PO
      1. USOleg Tarassov
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload