How do I display one drop-down list if one of two contitions are true.

[USAMax]

This one field is invalid if the other field does not say Web only or Both. I have a Name Range called WebCatListing that I want to display but only if one of these two options are checked.

Here is what I have:

=IF(OR(Q20 = "Both", Q20 = "Web only"),WebCatListing,"")

Thanks

 

[USAMax]

I'm sorry, I just answered my own question: Excel Data Validation -- Dependent Lists, http://www.contextures.com/xlDataVal02.html

I had to change, "Web only" to just Web then changed WebCatListing to Web. Then I created another Named Range using the same cells and called it Both.

For Data Validation I used List and =INDIRECT(Q20).

 

[juberpaul]

Hi,

I am still interested to know if there is a way to use 3 references for dependent data validation. Anyway, here are my predicaments:

1. I have 3 out of 10 categories that need dependent validation. For example, Present, Half-Day VL, Half-Day SL, Off, Holida, etc.
3. I need Present, Half-Day SL, and Half-Day VL to have the same drop-down.

Please help.

Please help!

 

[USAMax]

Hello Juberpaul,

First, one of the rules to this site is that you are not aloud to join in on an existing forum unless your helping with the first problem, you need to start a new thread. (So I've been told. LOL)
Second, the common way is explained in my link above.
And the third can be hard to understand if you have not been doing VBA for a long time.


This has been modified for my needs but you'll get the idea.

Sub DotComMakerLeftColumn(CCol, ArrayCount)
    Const wsN As String = "Sheet1"
    Dim Ws As Worksheet
    Set Ws = Sheets(wsN)
    
    Dim col As Collection
    Set col = New Collection
    Dim N As Long, i As Long, S As String
    '
    ' find header row
    Hdr = 2
    Do While Ws.Cells(Hdr, 1) = ""
        Hdr = Hdr + 1
    Loop
    
    ' get the data
    With Sheets(wsN)
        N = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = Hdr + 1 To N
            v = .Cells(i, "A").Value
            On Error Resume Next
            col.Add v, CStr(v)
            On Error GoTo 0
        Next i
    End With
    '
    ' remove duplicates
    ReDim ary(1 To col.Count) As Variant
    For i = 1 To col.Count
        ary(i) = col.Item(i)
    Next i
    '
    ' sort the array
    Call ShellSort(ary)
    '
    ' find grouping in header
    Field = Cells(16, CCol)
    cc = 1
    Do While Ws.Cells(Hdr, cc) <> Field
        cc = cc + 1
    Loop
    '
    'Update cells for the name range
    Ws.Range(Ws.Cells(8, cc), Ws.Cells(Ws.Cells(Rows.Count, cc).End(xlUp).Row, cc)).ClearContents
    For x = 1 To i - 1
        Ws.Cells(x + Hdr, cc) = ary(x)
    Next x
    '
    ' build the data validaton string
    'S = Join(ary, ",")
    '
    'create name range
    NewField = RemoveSpaces(Field)
    ActiveWorkbook.Names.Add Name:=NewField, RefersTo:="=OFFSET(" & wsN & "!$" & Chr(64 + cc) & "$" & Hdr + 1 & ",0,0,COUNTA(" & wsN & "!$" & Chr(64 + cc) & "$" & Hdr + 1 & ":$" & Chr(64 + cc) & "$" & (i + 8) - 1 & "),1)"
    '
    '
    ' set up the data validation
    With Cells(ActiveCell.Row, CCol).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & NewField
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
    End With
    If ActiveCell.Column = CCol Then
        FstValue = ary(1)
    End If
End Sub


Function DotComMakerLeftDependent(CCol, ArrayCount, RowOrDropDown As String)
    'THIS ROUTINE IS DEPENDENT ON THE COLUMN TO THE RIGHT OF THE SELECTED COLUMN
    
Dim Field As String
    Const wsN As String = "Sheet1"
    Dim Ws As Worksheet
    Set Ws = Sheets(wsN)
    
    Const TXsN As String = "wsN"
    Dim TXws As Worksheet
    Set TXws = Sheets(TXsN)
    Dim TXHR As Integer
    TXHR = 2                                                                                        'Start looking on Row 1 for the Header Row on the Taxonomy worksheet
    Do While TXws.Cells(TXHR, 1) = ""
        TXHR = TXHR + 1
    Loop
    Dim TXLC As Integer
    TXLC = TXws.Cells(TXHR, Columns.Count).End(xlToLeft).Column '
    
    
    Dim col As Collection
    Set col = New Collection
    Dim N As Long, i As Long, S As String
    'Dim ArrayCount As Integer
    LCell = ActiveCell.Offset(0, -1)
    
    
    Field = Cells(16, CCol)                            '
    MRow = FindMatchingHeader(TXws, TXHR, 1, TXLC, Field)                                        '   MerchHier Department
    '
    ' get the data
    If InStr(1, Field, "Sub-Class") > 0 Then Field = "Sub-Class"        'Clear up the header if the Header is Sub-Class (Merchandise Category)
    
    ArrayCount = ArrCount(Dependency)                               'YES: Get the number of arrays
    
    With TXws                                                           'Set the current sheet as Merchandiser Hieracary
        N = .Cells(Rows.Count, MRow).End(xlUp).Row                      '     Get the last row
        For i = 9 To N                                                  '     Have we gone through every row starting at row 8
            Dependent = True                                            '      NO: Set Dependent as TRUE to show that this is dependent on the column to the left
            For c = 1 To ArrayCount                             '          Have we gone through each array variable?
                If Dependent And Dependency(c) <> "" Then               '           NO: Is the Dependent still true and is the current array value NOT blank?
                        If .Cells(i, MRow - c) <> Dependency(c) Then '                 YES: Does the current value match the array?
                            Dependent = False                               '                     NO: Indicate that it does not match so it will stop looking.
                        End If
                End If                                                  '               END
            Next c                                                      '          CHECK AGAIN
            If Dependent Then                                           '

                If RowOrDropDown = "R" Then                                         'Was the Row requested?
                    DotComMakerLeftDependent = i
                    GoTo ExitWithRow:
                End If
                On Error Resume Next
                col.Add v, CStr(v)
                On Error GoTo 0
            End If
        Next i
    End With
    On Error GoTo 0
    '
    
    ' remove duplicates
    If col.Count > 0 Then
        ReDim ary(1 To col.Count) As Variant
        For i = 1 To col.Count
            ary(i) = col.Item(i)
        Next i
    Else
        Test = Test
    End If
    '
    ' sort the array
    Call ShellSort(ary)
    '
    ' find header row
    Hdr = 2
    Do While Ws.Cells(Hdr, 1) = ""
        Hdr = Hdr + 1
    Loop
    
    ' find grouping in header
    cc = 1
   Do While UCase(Ws.Cells(Hdr, cc)) <> UCase(Field)
   'TEST1 = Ws.Cells(Hdr, cc)
   'Cells(Hdr, cc).Select
        cc = cc + 1
    Loop
    '
    
    'Update cells for the name range
    Ws.Range(Ws.Cells(8, cc), Ws.Cells(Ws.Cells(Rows.Count, cc).End(xlUp).Row, cc)).ClearContents
    For x = 1 To i - 1
        If Not IsEmpty(ary(x)) Then
            Ws.Cells(x + Hdr, cc) = ary(x)
        End If
    Next x
    '
    ' build the data validaton string
    'S = Join(ary, ",")
    '
    'create name range
    NewField = RemoveSpaces(Field)
    SeeStr = "=OFFSET(" & wsN & "!$" & Chr(64 + cc) & "$" & Hdr + 1 & ",0,0,COUNTA(" & wsN & "!$" & Chr(64 + cc) & "$" & Hdr + 1 & ":$" & Chr(64 + cc) & "$" & (i + 8) - 1 & "),1)"
    ActiveWorkbook.Names.Add Name:=NewField, RefersTo:="=OFFSET(" & wsN & "!$" & Chr(64 + cc) & "$" & Hdr + 1 & ",0,0,COUNTA(" & wsN & "!$" & Chr(64 + cc) & "$" & Hdr + 1 & ":$" & Chr(64 + cc) & "$" & (i + 8) - 1 & "),1)"
    '
    'If x - 1 = 1 Then
    '    ActiveCell = ary(x - 1)
    'End If
    
    '
    ' set up the data validation
    With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & NewField
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
    End With
    If ActiveCell.Column = CCol Then
        FstValue = ary(1)
    End If
ExitWithRow:
End Function




Public Sub ShellSort(A() As Variant)
    Dim i As Long, J As Long, Low As Long, Hi As Long
    Dim Temp As Variant
  Low = LBound(A)
  Hi = UBound(A)
  J = (Hi - Low + 1) \ 2
  Do While J > 0
    For i = Low To Hi - J
      If A(i) > A(i + J) Then
        Temp = A(i)
        A(i) = A(i + J)
        A(i + J) = Temp
      End If
    Next i
    For i = Hi - J To Low Step -1
      If A(i) > A(i + J) Then
        Temp = A(i)
        A(i) = A(i + J)
        A(i + J) = Temp
      End If
    Next i
    J = J \ 2
  Loop
End Sub

 

[juberpaul]

Hi USAMax,

Thanks for the heads up. And your solution is hardcore. I will take note of that and will learn VBA in the future. THank you.