Results 1 to 4 of 4

Thread: Filter Data from Drop Down List
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filter Data from Drop Down List

    Hello All,

    This is what I need to do. I have a drop down list in sheet 1 that when a customer name is selected I want to copy in just the selected data for that customer name from another worksheet. I do not want to see all the records just the records associated for that customer.

    I've tried macros which work on the first selection, but any selection made afterwards it seems to keep the first customer name. Unable to clear and reset.

    Any ideas or direction on a better way. I can provide spreadsheet if need be.

    Thank you all,
    BD

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    248
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Filter Data from Drop Down List

    Hi bruce21,

    It sounds like you may want to use AGGREGATE. Take a look and tell me if this is what you are looking for.


    This is my data sheet with sales by name:
    A B C D E
    3 Name Company Sales 2017 Sales 2018 Sales 2019
    4 Alf Carruthers ACME Widgets $21,315 $30,346 $21,994
    5 Bert Jones Altwood Bits $38,201 $25,608 $59,469
    6 Alf Carruthers Carter PLC $41,455 $70,711 $40,436
    7 Alf Carruthers Green & Son $46,016 $15,851 $66,310
    8 Bert Jones Mint Trading $40,345 $24,567 $82,349
    9 Alf Carruthers Bridges Stuff $70,662 $64,842 $46,153
    10 Bert Jones Jones & Jones $31,668 $82,986 $33,251
    11 Kathy Bridges Jones & Jones $33,857 $69,499 $84,756
    12 Kathy Bridges Zoe & Partners $15,573 $46,401 $68,767
    13 Kathy Bridges Lily Interiors $65,996 $82,312 $41,678
    14 Kathy Bridges Easy Partners $72,262 $48,258 $49,174
    15 Sarah Pollard ACME Widgets $19,159 $68,318 $65,540
    16 Kathy Bridges Jones & Jones $62,291 $52,788 $38,300
    SalesData

    From your dropdown in B3 on your main sheet you select the name. The formulae in D3 to G3 (copied down as many hits as you may have) will show just the rows matching the selected name.

    A B C D E F G
    2 Company Sales 2017 Sales 2018 Sales 2019
    3 Name= Kathy Bridges Jones & Jones $33,857 $69,499 $84,756
    4 Count= 5 Zoe & Partners $15,573 $46,401 $68,767
    5 Lily Interiors $65,996 $82,312 $41,678
    6 Easy Partners $72,262 $48,258 $49,174
    7 Jones & Jones $62,291 $52,788 $38,300
    8
    bruce21

    Worksheet Formulas
    Cell Formula
    D3 =IF(ROWS($D$3:$D3)>$B$4,"",INDEX(SalesData!B$4:B$999,AGGREGATE(15,6,ROW(SalesData!$A$4:$A$999)-ROW($A$3)/(SalesData!$A$4:$A$999=$B$3),ROWS(D$3:D3))))
    E3 =IF(ROWS($D$3:$D3)>$B$4,"",INDEX(SalesData!C$4:C$999,AGGREGATE(15,6,ROW(SalesData!$A$4:$A$999)-ROW($A$3)/(SalesData!$A$4:$A$999=$B$3),ROWS(E$3:E3))))
    F3 =IF(ROWS($D$3:$D3)>$B$4,"",INDEX(SalesData!D$4:D$999,AGGREGATE(15,6,ROW(SalesData!$A$4:$A$999)-ROW($A$3)/(SalesData!$A$4:$A$999=$B$3),ROWS(F$3:F3))))
    G3 =IF(ROWS($D$3:$D3)>$B$4,"",INDEX(SalesData!E$4:E$999,AGGREGATE(15,6,ROW(SalesData!$A$4:$A$999)-ROW($A$3)/(SalesData!$A$4:$A$999=$B$3),ROWS(G$3:G3))))
    B4 =COUNTIF(SalesData!A:A,B3)

    Only the INDEX column changes (B,C,D,E as they're all next to each other) between D and G.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,140
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Filter Data from Drop Down List

    I can provide spreadsheet if need be.
    It would be helpful if you could upload a sample workbook to a site such as dropbox.com then put the link here.


  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,173
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Filter Data from Drop Down List

    Another approach is a parameterised query. Uses no formulas & no code.
    Please google for examples. Like http://dailydoseofexcel.com/archives...-data-queries/
    To receive a better answer, put more work into asking the question.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •