Results 1 to 2 of 2

Thread: Question : Multiple Criteria for Data Validation

  1. #1

    Question : Multiple Criteria for Data Validation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I'm new on this forum. I have a problem in getting rows from a multiple criteria. Below is my source table:

    Sheet 1
    Resource Region Name
    DBA Europe John
    DBA Asia David
    QA Africa Paul
    DBA Asia William
    QA Asia Henry
    QA Europe Mirla











    Sheet 2:

    I have 3 cells A, B and C.

    A is for Resource and B is for Region. If I select DBA in cell A and Asia in cell B, then David and William will be added on my dropdown list for cell C. Someting like this:


    A B C
    Resource Region Name
    <dropdown> <dropdown> <dropdown>






    Please help me in using OFFSET and MATCH functions for this.

    Thank you in advance.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    In Sheet 1, create another column, say column K to list the names for data validation population, and insert formula in K2:

    =IFERROR(INDEX($C$2:$C$7,SMALL(IF($A$2:$A$7=Sheet2!$A$2,IF($B$2:$B$7=Sheet2!$B$2,ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($K$2:$K2))),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER.
    Then copy down as far as the original Name list.

    Now name this column, Name. Do this by select column K and typing the word Name in the Name Box just to the left of the formula bar.

    Now for the Data Validation in Sheet2. Use List and formula:

    =OFFSET(Name,1,0,COUNTIF(Name,"?*")-1,1)


Posting Permissions

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