+ Reply to Thread
Results 1 to 4 of 4

Dependent Dropdown List Using Index and Small Won't Return Multiple Values

  1. #1
    Registered User
    Join Date
    09-04-2013
    Location
    Califronia, United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Dependent Dropdown List Using Index and Small Won't Return Multiple Values

    I am using Excel 2010:
    I have two worksheets:
    Worksheet "Lists": contains the list data for the dropdowns
    1. Named Range "QualList"- =Lists!$A$1:$A$128 contains a list of qualifications that set up the first dropdown list, which I have done using data validation/List/"Named Range"
    2. Named Range "Master"- =Lists!$C$1:$F$1620 contains the master list of Employees, the district on which they work and the qualifications.

    Worksheet "Data Entry" has the three columns which contain separate dropdown lists:
    1. Column A under "Qual" (as identified above using ="Named Range").
    2. Column B under "Dist" which will be dependent on which qualification is selected in the adjacent cell in column A.
    3. Column C under "Employee" which will be dependent upon column A and column B. It must go in that order, Column A, Column B, Column C.

    The master list has more than one instance of each employee, district and qualification. For example: for the qualification of ASGS, two employees work on district "YICC" and one works on district "SO".
    On worksheet "Data Entry", I need to first identify a qualification, which I have done.

    There are too many qualifications to try and break all the information out into separate District/Employee lists for each qualification, as this data changes often and is dependent on a manual import from an external source (can't tap into it for auto import).

    I have tried the following formula and can get it to work in successive cells, but cannot get it to work in Data Validation with dropdown lists. I can only get it to populate the first instance in the District List.

    =INDEX(Master,SMALL(IF(Master=$A$2,ROW(Master)),ROW(1:1)),2)

    I have attached a workbook. Any ideas?

    Range "QualList"QualDataExample.xlsmQualDataExample.xlsm
    Last edited by ebevis; 10-09-2013 at 09:51 PM.

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Dependent Dropdown List Using Index and Small Won't Return Multiple Values

    Are you looking for this!!
    Attached Files Attached Files
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    09-04-2013
    Location
    Califronia, United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Dependent Dropdown List Using Index and Small Won't Return Multiple Values

    Yes. This works great. Thank you very much. Is it as simple as putting the "= "ListName" in Data Validation?

  4. #4
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Dependent Dropdown List Using Index and Small Won't Return Multiple Values

    You are Welcome ebevis

+ 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. return multiple matches using INDEX and SMALL
    By merlyn45 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-31-2013, 05:14 AM
  2. Creating Multiple and Dependent Dropdown list
    By Mythri Hegde in forum Excel General
    Replies: 9
    Last Post: 04-18-2012, 02:57 PM
  3. Unhiding columns on multiple tabs dependent on selection of dropdown list in 1 tab
    By gazzz_tha1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-12-2011, 06:26 PM
  4. Using Index & Small to return multiple values
    By BHudPE in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2010, 06:06 AM
  5. Return Multiple Values Based On Dropdown List Selection
    By tigabalm in forum Excel General
    Replies: 5
    Last Post: 04-01-2010, 07:36 PM

Tags for this Thread

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