Contextures - Limit Selection List DV0023 Query

[tomo1243]

Hello Excel Gurus,

This is a question on Limit Selection List which is taken from something that I found on Contextures spreadsheet examples DV0023. I am using Excel 2007 on Windows 7. In the example, it uses an advanced filter to create a short list using Event code. The event code uses a character that is entered into cell B3 (Name) on the "Summary" sheet and then looks up all the names starting with that letter from the sheet "Sales Data". When I look at the code that runs, it only checks data in Cell B3. I want to know if it's possible to have this dynamically change based on which row you are in ie if you have a formula you can copy it down so that instead of referring to say B3, it's B4 or B5 or B6 - whichever row you are in. It is possible to change the code to be active sheet & current cell lookup and place the option list in the cell to the right?

The example workbook is available from http://www.contextures.com/excelfiles.html and search for DV0023.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsSD As Worksheet
Dim wsS As Worksheet
Set wsSD = Sheets("Sales Data")
Set wsS = Sheets("Summary")

If Target.Address = "$B$3" Then
  wsS.Range("C3").Value = ""
  wsSD.Range("Database").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=wsS.Range("B2:B3"), _
    CopyToRange:=wsSD.Range("J1"), Unique:=True
End If
End Sub

Thank you in advance,
Regards Tom

 

[Marcol]

Try this workbook downloadable from My Skydrive
DataValSelectName

Creates dynamic Drop-down Lists dependent on Initial Letters.
If Column B is blank the the whole list is returned in Column C.

Enter a Letter in Column A to trigger the dropdowns.

This is not an adaption of your Contextures workbook, although I have used the same Data, (thanks Debra).

All the named lists used are dynamic and non-volatile, this may slow the opening of larger files, but once opened the file will significantly out perform similar files that use INDIRECT(), OFFSET(), etc

Hope this helps.

 

[tomo1243]

Hi Marcol,

Thanks for the reply, this does certainly help and it works perfectly. Tomorrow I will try and update this to have two drop down boxes on the Summary tab. It looks like I will need to clone ByInitials & Namelist and make duplicate columns on Summary and DDCtrl tabs and then update the formulas. It's just the VB code that I am not sure of. I think that I can just use the below or is there anything else I need to do?

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Row < 3 Then Exit Sub
    
    If Target.Column = 2 Then
        Target.Offset(0, 1).Value = ""
        Sheets("DDCtrlA").Range("B1") = Target

    If Target.Column = 7 Then
        Target.Offset(0, 1).Value = ""
        Sheets("DDCtrlA").Range("G1") = Target
    End If
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Row < 3 Then Exit Sub
    ' This forces the change event.
    If Target.Column = 3 Then
        Sheets("DDCtrlA").Range("B1") = Target.Offset(0, -1)
    If Target.Column = 8 Then
        Sheets("DDCtrlA").Range("G1") = Target.Offset(0, -1)
    End If

End Sub

Cheers - Tom

 

[Marcol]

You must end the If statements

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Row < 3 Then Exit Sub
 
    If Target.Column = 2 Then
        Target.Offset(0, 1).Value = ""
        Sheets("DDCtrlA").Range("B1") = Target
    End If
    If Target.Column = 7 Then
        Target.Offset(0, 1).Value = ""
        Sheets("DDCtrlA").Range("G1") = Target
    End If
 
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    If Target.Row < 3 Then Exit Sub
    ' This forces the change event.
    If Target.Column = 3 Then Sheets("DDCtrlA").Range("B1") = Target.Offset(0, -1)
    If Target.Column = 8 Then Sheets("DDCtrlA").Range("G1") = Target.Offset(0, -1)
 
End Sub

Post again if you run into trouble.