+ Reply to Thread
Results 1 to 7 of 7

Drop Down List populated from Index Match

  1. #1
    Registered User
    Join Date
    03-02-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    3

    Drop Down List populated from Index Match

    Hello

    I was wondering if anyone can help me populate my drop down list.

    I have 2 columns. I want to populate my dropdown list with data from column a that has a specific value in column b.

    is this possible with out using VBA?

    eg.

    Column A / Column B

    123 / TEST
    126 / TEST
    100 / BACON
    231 / CHEESE


    basically I would like a drop down list that contains the constants from column b. so from the example above it should contain

    123 and 126 as options.

    Also can someone recommend a good tutorial book for excel?

    Thanks

    jonesyp

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Drop Down List populated from Index Match

    Hi,

    By "constants', do you mean duplicates? If so, the attached workbook should solve your problem.

    The purple cells contain the array formula to create a list of items in column A that match up with duplicate entries in column B (note that the formula extends longer than it necessarily has to). I have then created a named range (called "DataList"), which takes all of the purple cells and ignores the blanks at the bottom of the list, using the OFFSET function. This named range is then the source of your data validation list in the orange cell. Note that the purple column can be hidden if necessary.

    I hope this helps

    P.S. Might I also add...how good is bacon and cheese!?!?!?!?

  3. #3
    Registered User
    Join Date
    03-02-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Drop Down List populated from Index Match

    Yeah I was looking for the duplicates,

    It unfortunately didn't work as I was looking for.

    Currently I have used the following code and this returns just one value

    Please Login or Register  to view this content.
    now this gives the first value I am looking for, I just need it to populate the others in the list.

    Thanks for your help
    Last edited by jonesyp; 03-03-2014 at 02:13 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Drop Down List populated from Index Match

    How didn't it work as it was supposed to? It returns a list with 123 and 126 as options, just as you asked for...

    Your MATCH formula isn't working because MATCH will only return the first instance of a value that it finds. So it will return 123 as this is first instance of "TEST" in B1:B4.

    Can you please explain to me what isn't right about my previously uploaded workbook, so that I may help you figure this out

  5. #5
    Registered User
    Join Date
    03-02-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Drop Down List populated from Index Match

    So I have worked out a basic solution, it requires me to have it filted in a list.

    =OFFSET((INDIRECT("'"&$G1&"'!B$2")),MATCH(B2,CATAGORY,0)-1,-1,COUNTIF(CATAGORY,B2),1)

    using this website

    http://www.contextures.com/xlDataVal13.html

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Drop Down List populated from Index Match

    Thanks for the link, it will help people in future who have the same query.

    Please mark this thread as solved, and just to let you know, you can add to the reputation of those people who have helped you by clicking on the * next to their post, and it is also another way of saying thanks.

    Have a great day

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Drop Down List populated from Index Match

    You need to set up the DD list based on where you have that formula of aj's

    This will also work, based on youyr sample data...
    A
    B
    C
    3
    123 TEST
    123
    4
    126 TEST
    126
    5
    100 BACON
    6
    231 CHEESE


    C3=IFERROR(INDEX($A$3:$A$6,SMALL(IF($B$3:$B$6=B3, ROW($A$1:A1)),ROW($A1))),"") ARRAY formula, copied down
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Replies: 2
    Last Post: 02-12-2014, 01:12 PM
  2. [SOLVED] Index Match from a cell populated from index match
    By MarcLewis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-15-2013, 05:30 AM
  3. Two Drop Down List and INDEX, MATCH FUNCTION
    By Devi Suryani in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-18-2013, 02:37 AM
  4. [SOLVED] Drop-down list Populated by a Subset of a larger list
    By RJH in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-11-2010, 07:17 AM
  5. Drop-down list populated by SQL recordset?
    By LadyReader in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-09-2009, 08:33 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