+ Reply to Thread
Results 1 to 8 of 8

How to make dropdownt list of unique items dependent on another dropdown list?

  1. #1
    Registered User
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2016
    Posts
    19

    How to make dropdownt list of unique items dependent on another dropdown list?

    Hello,
    I got a problem and even thought I looked over the internet I wasnīt able to find solution. I made an example of my problem for which I need solution. So letīs say I got a table with two columns named Shop and Item. And I got random data in it. I made a dropdown of with unique names of shops. Here is the problem I need to make second dropdown with unique names of items in that specific shop selected from the dropdown I made. So for example I click and select Albert from the dropdown and I will get to choose from: glue, scissors, rubber and string.
    Thanks for any advice
    (I include excel with my problem )
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: How to make dropdownt list of unique items dependent on another dropdown list?

    This is one way.

    With unique Shop names as headers in P:S this array-entered formula returns lists of unique Items associated with each Shop. Array-enter in P4 fill across and down until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    Then with K5 the active cell go to Data Validation > Allow: List and put this formula in Source:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: How to make dropdownt list of unique items dependent on another dropdown list?

    By the way j_analyst I failed to mention, welcome to the forum.

    This is another way. Personally I like it better. Additionally one side effect is that it alphabetizes the Items. See what you think.

    The setup and drop down formula are the same as in post #2.

    The formula for the lookup table is still array-entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2016
    Posts
    19

    Re: How to make dropdownt list of unique items dependent on another dropdown list?

    Thank you very much for your reply. I tried to replicate your process. And I found a trouble. When I enter the formula into array I get "glue" on every position. I think its because number in this part ROWS($4:4) remains constant even though it should change accordingly.

  5. #5
    Registered User
    Join Date
    11-20-2016
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    4

    Re: How to make dropdownt list of unique items dependent on another dropdown list?

    Another way would be to make a second filtered list and use an array formula. You'd then use data validation on K5 and list its input range as 04-08

    O3 = "Filtered List 2"
    04 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(1:1))-3,2)}
    05 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(2:2))-3,2)}
    06 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(3:3))-3,2)}
    07 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(4:4))-3,2)}
    08 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(5:5))-3,2)}
    Attached Files Attached Files
    Last edited by TheSubject; 11-20-2016 at 06:30 PM.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: How to make dropdownt list of unique items dependent on another dropdown list?

    Quote Originally Posted by j_analyst View Post
    Thank you very much for your reply. I tried to replicate your process. And I found a trouble. When I enter the formula into array I get "glue" on every position. I think its because number in this part ROWS($4:4) remains constant even though it should change accordingly.
    That happens when you array enter over the entire range at once.

    You have to remedy that first.

    Pre-select all the problem output.
    With U4 the active cell hit the F2 function key.
    While in edit mode hold down just Ctrl while hitting enter.
    You should see all blank cells ... the effect of IFERROR.

    Now select just U4.
    Go into edit mode.
    Array enter that single cell.
    Fill down and across.

  7. #7
    Registered User
    Join Date
    11-01-2016
    Location
    Prague
    MS-Off Ver
    2016
    Posts
    19

    Re: How to make dropdownt list of unique items dependent on another dropdown list?

    Thank you very much I was able to aplicate your process to my bigger project and it works perfectly.
    EDIT: Solved
    Last edited by j_analyst; 11-21-2016 at 11:46 AM.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: How to make dropdownt list of unique items dependent on another dropdown list?

    You are welcome. I am glad to hear it does the job. Thank you for the feedback, marking your thread SOLVED and of course the rep.

+ 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. Create dropdown list in Excel that will only list unique entries
    By MissAudrey in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-22-2015, 04:56 PM
  2. Replies: 1
    Last Post: 03-18-2014, 02:19 PM
  3. Problem getting conditional formatted dropdown list items to keep their formatting in list
    By scottatbuckman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2014, 02:39 PM
  4. Unique list of records dropdown in Data Validation list
    By sghosh12 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-13-2013, 08:27 AM
  5. [SOLVED] Counting # of Unique Items In List & Listing Unique Items In A List
    By unpluggedmusic in forum Excel General
    Replies: 3
    Last Post: 12-05-2012, 11:44 AM
  6. Replies: 21
    Last Post: 12-02-2009, 03:27 PM
  7. Replies: 10
    Last Post: 03-19-2007, 11:30 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