+ Reply to Thread
Results 1 to 6 of 6

Dropdown list dependent on 2 criterias

  1. #1
    Registered User
    Join Date
    11-04-2019
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    3

    Dropdown list dependent on 2 criterias

    Hello everyone,

    I would like to create a list dependent on 2 criterias. But i would like to avoid dependent drop-down lists, since i have about 1000 rows of data and 400 unique productcodes.

    As presented by my mock data, there are 3 columns of interest:

    Column 1: Region
    Column 2: Productcode
    Column 3: Category

    Actually i would like the function like Vlookup, but then return mutiple values correspondonng to the criterias.


    I hope I have explained my problem satisfactory.

    Example is attached.

    Best regards
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Dropdown list dependent on 2 criterias

    I've created helper column G like:
    into G2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    That's array formula so you have to accept it with Ctrl+Shift+Enter not just Enter.
    Then drag it down for ex. 10 rows down.
    Marked G2:G10 (as down as you drag it down as above) and name this range for ex. as dropData.
    Then recreate this range as dynamic. Formulas / Name manager and change refres to for dropData into:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then ifor cell C2 in Data / Data validation choose Source as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Check attached file.
    Attached Files Attached Files
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    11-04-2019
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    3

    Re: Dropdown list dependent on 2 criterias

    Thank you so much! Just what i needed!
    Do you know if there's a way to only return unique values in the "Helper" directly in the formula?

    Otherwise i have a new formlua returning unique values:
    Please Login or Register  to view this content.
    //DKOG
    Last edited by DKOG; 11-06-2019 at 10:58 AM.

  4. #4
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Dropdown list dependent on 2 criterias

    I don't see duplicates on helper column? Do you need check for unique?

  5. #5
    Registered User
    Join Date
    11-04-2019
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    3

    Re: Dropdown list dependent on 2 criterias

    Yes I am in the process of cleaning up the data. But for the time being, I would like to check for unique values
    for example if USA123 is chosen, 2 times Clothes comes up.

    //DKOG
    Attached Files Attached Files

  6. #6
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Dropdown list dependent on 2 criterias

    Simplest way is to apply helper2 where you can extract unique, as you suggest in post #3.
    Check attached file.
    Attached Files Attached Files

+ 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] Multiple Dependent Dropdown List
    By hanif in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2018, 08:47 AM
  2. Dependent Dropdown List
    By rjcjason in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-07-2018, 10:51 AM
  3. Replies: 1
    Last Post: 06-04-2018, 06:16 PM
  4. Dependent dropdown list
    By Spikyd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-15-2017, 08:09 AM
  5. [SOLVED] Dependent Dropdown-List problem
    By Challebjoern in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2017, 11:45 AM
  6. Replies: 7
    Last Post: 11-21-2016, 04:40 PM
  7. dropdown list based on 2 criterias
    By gnulab in forum Excel General
    Replies: 0
    Last Post: 07-26-2011, 02:16 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