none
Filter relationships by using drop-down lists RRS feed

  • Question

  • I have a worksheet having columns and rows; no surprise there.  Please reference attached snapshot.

    The columns are labeled Rep, RM, KSA1, KSA2, etc.  In the rows that follow, the Rep includes Rep1, Rep2, etc., RM includes RM1, RM2, etc, and each KSA includes a designation 3 (or blank) that relates all.

    I wish to include pull-downs that would have a user choose an RM and KSA, then a list of all related Reps would be displayed.

    I realize my explanation is lacking, so please review the attached snapshot.  I realize filtering is possibly an easier approach, but I am hoping for the described solution.

    Thank you for your support.

    Rich

    Monday, September 10, 2018 11:46 AM

Answers

  • See the sample workbook at https://www.dropbox.com/s/ug5sn02x20mftvy/FilterByDropdown.xlsm?dl=1

    You'll have to enable macros when you open the workbook. It uses the Worksheet_Change event to create the list, using Advanced Filter:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("C14,C16"), Target) Is Nothing Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Range("C17").Value = "REP"
            Range("D17").Value = "RM"
            Range("D18").Value = Range("C14").Value
            Range("E17").Value = Range("C16").Value
            Range("E18").Value = 3
            Range("A1").CurrentRegion.AdvancedFilter _
                Action:=xlFilterCopy, _
                CriteriaRange:=Range("D17:E18"), _
                CopyToRange:=Range("C17"), _
                Unique:=True
            Range("C17,D17:E18").ClearContents
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by RichMWilliams Tuesday, September 11, 2018 7:52 AM
    Monday, September 10, 2018 3:16 PM
  • I don't see a worksheet 'SME per region' in your sample workbook, so I created it. The code belongs in the worksheet module of 'SME per region'.

    I used cells B1 and B3 for the KSA and RM dropdowns, and the results will be pasted from B5 down.

    If you change the layout of 'SME per region', you will have to modify the code accordingly.

    I used formulas =E1 in E4 etc., and hid the results by applying the custom number format ;;; (three semi-colons).

    See https://www.dropbox.com/s/69zw9vjx65zsndv/KM%20Sample.xlsm?dl=1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by RichMWilliams Thursday, September 13, 2018 10:35 AM
    Wednesday, September 12, 2018 8:53 AM

All replies

  • See the sample workbook at https://www.dropbox.com/s/ug5sn02x20mftvy/FilterByDropdown.xlsm?dl=1

    You'll have to enable macros when you open the workbook. It uses the Worksheet_Change event to create the list, using Advanced Filter:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("C14,C16"), Target) Is Nothing Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Range("C17").Value = "REP"
            Range("D17").Value = "RM"
            Range("D18").Value = Range("C14").Value
            Range("E17").Value = Range("C16").Value
            Range("E18").Value = 3
            Range("A1").CurrentRegion.AdvancedFilter _
                Action:=xlFilterCopy, _
                CriteriaRange:=Range("D17:E18"), _
                CopyToRange:=Range("C17"), _
                Unique:=True
            Range("C17,D17:E18").ClearContents
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by RichMWilliams Tuesday, September 11, 2018 7:52 AM
    Monday, September 10, 2018 3:16 PM
  • Hans,

    Thank you for having shared a solution that works very well and is so elegant.

    Unfortunately, I do need a bit of assistance.  I failed to mention that I wish to have the filtering displayed on a separate worksheet.  The worksheet with the data is named "Knowledge Map".  The user would use a worksheet named "SME per Region" to choose the RM and KSA, then be presented with the Reps (results).

    Please reference attached snapshot of the "Knowledge Map" worksheet.

    I have been running around in circles trying to figure out how to use your solution in the manner I have just mentioned, but no luck.  Would you please lend me your support?

    Thanks again,

    Rich

    Tuesday, September 11, 2018 8:40 AM
  • Which range contains the KSAs? Is that E1, F1, G1 etc.? If so, the code I wrote won't work - it would expect them to be in row 4 (the header row of your table). But we may be able to get around that.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 11, 2018 12:39 PM
  • Hi Hans,

    Yes, you are correct, the headers for the KSAs are found in E1, F1, G1, etc.  I tried copying those headers adding =E1, =F1, =G1, etc. to the corresponding E4, F4, G4 cells, but no luck.

    Thanks again for your continued help.

    Best regards,

    Rich

    Tuesday, September 11, 2018 12:56 PM
  • Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 11, 2018 2:45 PM
  • Hi Hans,

    The uploaded stripped-down copy is found here:

    https://drive.google.com/open?id=1_L1qOnmQIUxKbJg3SAD1BWPHvt_FnE3R

    I have visited Eileen's Lounge and will definitely register.  Thank you.  I noticed that you are in The Netherlands.  My wife and I resided for a few years in Wassenaar.  What a beautiful country.

    Dank u wel,

    Rich

    Tuesday, September 11, 2018 11:46 PM
  • I don't see a worksheet 'SME per region' in your sample workbook, so I created it. The code belongs in the worksheet module of 'SME per region'.

    I used cells B1 and B3 for the KSA and RM dropdowns, and the results will be pasted from B5 down.

    If you change the layout of 'SME per region', you will have to modify the code accordingly.

    I used formulas =E1 in E4 etc., and hid the results by applying the custom number format ;;; (three semi-colons).

    See https://www.dropbox.com/s/69zw9vjx65zsndv/KM%20Sample.xlsm?dl=1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by RichMWilliams Thursday, September 13, 2018 10:35 AM
    Wednesday, September 12, 2018 8:53 AM
  • Hi Hans,

    The code works very well.  Thank you!

    I have attempted to create an additional drop-down list for the RD field, but I've been unable to locate the connection between the drop-down lists for the KSA and RM fields.

    How are the drop-down lists associated with the KSA and RM columns residing on the 'SME per Region' worksheet?

    Thank you,

    Rich
    Thursday, September 13, 2018 10:35 AM
  • See columns H and I on the 'SME per Region' sheet. I simply copied the values there, then defined dynamic named ranges KSA and RM; you can see their definition in Formulas > Name Manager.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 13, 2018 2:53 PM
  • Thanks, Hans.

    I now see how you have defined the ranges for KSA and RM using Name Changer, and with your help I have been able to create a new defined range (named 'RD', in column J of 'SME per Region' sheet), but I have been unable to determine how to create the drop-down field that would connect to the new RD defined range.  Your teaching woudl be greatly appreciated.

    Thank you,

    Rich

    Friday, September 14, 2018 11:09 AM
    • Select the cell where you want the dropdown.
    • On the Data tab of the ribbon, click Data Validation.
    • Select List from the Allow dropdown.
    • Enter  =RD  in the Source box.
    • Click OK.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 14, 2018 11:46 AM