+ Reply to Thread
Results 1 to 6 of 6

Show a list of data based from dropdown selection

  1. #1
    Registered User
    Join Date
    11-08-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    2

    Show a list of data based from dropdown selection

    Hi, I need help on getting the proper and simpler formulas on how to extract data and show it based on a selection in a dropdown. Please refer to the sample excel file below. If i choose Entrepreneurship Level 2 in the dropdown it should be able to list down every behavior that is level 2 from the table in sheet 2. Please help.
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,235

    Re: Show a list of data based from dropdown selection

    Put in E3 on Sheet1 and copied down until blank results

    =IFERROR(INDEX(Sheet2!$A$2:$A$17,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$17)-MIN(ROW(Sheet2!$A$2:$A$17))+1)/(Sheet2!$B$2:$B$17=--RIGHT($E$1,1)),ROW(A1))),"")

  3. #3
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Show a list of data based from dropdown selection

    Hi,

    This involves a complicated formula that is essentially INDEX MATCH multiple returns.

    See this website for explanation:

    https://fiveminutelessons.com/learn-...le-values-list

    See attached file for a working example with your data.

    Hope this is helpful.

    Cheers
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Show a list of data based from dropdown selection

    See the attached file.

    I used conditional formatting to update a border on your table as the formulas fill in the data, feel free to hide the helper column and your list if needed, was more or less an example. I removed the merged cell, simply because I don't like merged cells, feel free to re-merge it.

    the main formula is:

    Please Login or Register  to view this content.
    and the helper column is:

    Please Login or Register  to view this content.
    I also used this formula to add borders to your table of results as they are filled in:

    Please Login or Register  to view this content.
    I did it without an array formula also, which will make maintenance easier.

    Attached Files Attached Files
    Last edited by TheN; 11-08-2016 at 11:18 PM. Reason: more info

  5. #5
    Registered User
    Join Date
    11-08-2016
    Location
    Philippines
    MS-Off Ver
    2010
    Posts
    2

    Re: Show a list of data based from dropdown selection

    Thank you for your input! Appreciate it!

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Show a list of data based from dropdown selection

    Here's another one...

    Entered in E3:

    =IF(ROWS(E$3:E3)>COUNTIF(Sheet2!B:B,RIGHT(E$1)),"",INDEX(Sheet2!A:A,MATCH(--RIGHT(E$1),Sheet2!B:B,0)+ROWS(E$3:E3)-1))

    Copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Limit dropdown list based on selection from another list
    By msmetal in forum Access Tables & Databases
    Replies: 5
    Last Post: 01-23-2015, 11:22 PM
  2. [SOLVED] Populating information based on selection from dropdown list
    By kearbear in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-10-2013, 11:16 PM
  3. Replies: 6
    Last Post: 05-31-2013, 11:17 AM
  4. Cull dropdown list options based on selection in another dropdown
    By Kiffar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2012, 01:53 AM
  5. Update a cell based on selection in dropdown list.
    By ladykickbox in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-26-2012, 07:45 AM
  6. Data plotted on XY scatter graph based on selection from dropdown list
    By melrosetutu in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-26-2012, 03:39 PM
  7. Show specific rows and colums on dropdown list selection
    By info5 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-07-2012, 02:22 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1