+ Reply to Thread
Results 1 to 4 of 4

Match a person from a matrix [need dynamic dropdown list]

  1. #1
    Registered User
    Join Date
    02-11-2020
    Location
    Slovenia
    MS-Off Ver
    2016
    Posts
    3

    Match a person from a matrix [need dynamic dropdown list]

    Hello

    I have a problem that i can`t solve. I have a matrix of people that are educated in a different technique. For every person that is educated in the matrix he receives a number 1. The person that is not educated receives a number 0.
    So what i want is to have is a formula to insert in data validation in drop down list and in the drop down list there must be only that persons who are educated (they have the number 1). Please see the attachment

    =IF(AND($O$9="Method1";$C$5:$C$6=1);B5:B6;IF(AND($O$9="method2";$D$5:$D$6=1);B5:B6;""))
    I do get the correct answer for the 1. and 2. person but the problem is when the formula get to the person with a 0; the formula is false and i get a blank answer.
    How do i make a formula to dynamically look for number 1 and the it gives me all the person who have the number 1.

    Thank you guys very much for your help.

    Kind regards, Pavel
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Match a person from a matrix [need dynamic dropdown list]

    with helper range
    =IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$5:$B$29)/(INDEX($C$5:$K$29,,MATCH($O$9,$C$4:$K$4,))=1),ROWS($P$8:P8))),"")
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-11-2020
    Location
    Slovenia
    MS-Off Ver
    2016
    Posts
    3

    Re: Match a person from a matrix [need dynamic dropdown list]

    Thank you very much for this formula it works in my spreadsheet! Now i have a new problem. I would like that $O$9 to be dynamic.
    I have a table were a user chooses a method (method 1 to 9) and then in the drop down i would like to have all educated persons defined in the matrix. Is that even possible with this formula?
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Match a person from a matrix [need dynamic dropdown list]

    Reading the description in post #3 and then looking at the file I am confused.
    The description indicates that you want to use a drop down in cell O9 on Sheet1 and populate cells P9 and down (which could be done using Tim's formula).
    Looking at the file it seems more likely that you want to populated cells C4:C11 on the Master Table sheet with drop downs corresponding to the Method number in cells B4:B11
    Please clarify which of the above is correct.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Dynamic list within list from matrix
    By philiplaw in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-18-2018, 07:20 AM
  2. [SOLVED] Create dynamic list by looking up matrix
    By Lana14 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-27-2017, 07:34 AM
  3. [SOLVED] Populating a Matrix using a dynamic list
    By dcossyl in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-19-2015, 01:06 PM
  4. Creating a dynamic dropdown list of a selection of a master list
    By RooMar in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 05-15-2015, 05:27 AM
  5. Replies: 1
    Last Post: 04-14-2014, 01:47 PM
  6. [SOLVED] match formula to find total score of a person appearing more than once in list
    By rohit43 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-01-2013, 03:53 AM
  7. [SOLVED] Find lowest score and match to person from list
    By jndipworm in forum Excel General
    Replies: 5
    Last Post: 08-09-2012, 07:25 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