+ Reply to Thread
Results 1 to 7 of 7

Data validation list of subset

  1. #1
    Forum Contributor
    Join Date
    03-04-2013
    Location
    Europe
    MS-Off Ver
    Office 365 ProPlus
    Posts
    147

    Data validation list of subset

    Hi,

    I have made an example workbook
    example workbook.xlsx

    In cell D4 I would like to make a data validation list (dropdown) that would only show the animals with the color specified in the data validation list in cell C4 (based on the table below).

    It also needs to be noted that the number of animals/entries are "unknown", i.e. I would like to use cell B4 as an offset (see data validation list formula for D4).

    Thankful for any help.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Data validation list of subset

    For D4 use the Source of

    =OFFSET($B$8,0,1,$B$4)
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Forum Contributor
    Join Date
    03-04-2013
    Location
    Europe
    MS-Off Ver
    Office 365 ProPlus
    Posts
    147

    Re: Data validation list of subset

    Thanks for your reply Speshul, but you misunderstood my question.

    I only want the data validation list of D4 to display the animals with the corresponding color chosen in the list in C4.

    For example:
    If i choose "Red" in C4
    i want the D4 list to show
    Fish
    Shark
    Tiger
    Hippo
    Snake
    Scorpion

    I do _not_ just want a list of all animals in the D column in the D4 data validation list.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Data validation list of subset

    Here's one way. In E8 copied down, is this Arrayed Formula

    =IFERROR(INDEX($C$8:$C$20, SMALL(IF($B$8:$B$20=$C$4, ROW($B$8:$B$20)-ROW($B$8)+1),ROWS($A$1:$A1))),"")
    ...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.

    I then defined a dynamic range called SubList
    =OFFSET(Blad1!$E$8,0,0, SUMPRODUCT(--(LEN(Blad1!$E$8:$E$20)>1)))

    Then just used simple DataValdiation >Use List > =SubList
    You can hide Column E if you like
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    03-04-2013
    Location
    Europe
    MS-Off Ver
    Office 365 ProPlus
    Posts
    147

    Re: Data validation list of subset

    Thanks ChemistB, that way I already knew of, i was thinking if there was a smart way of getting the subset directly in the data validation formula, but it doesn't seem to be

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Data validation list of subset

    I believe the list must be a range of congruent cells.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Data validation list of subset

    worksheet_Change event can be used, which does not require Helper column
    Code
    Please Login or Register  to view this content.
    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. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  2. Replies: 3
    Last Post: 04-11-2011, 05:52 PM
  3. [SOLVED] Data Validation List (Subset)
    By AlexRoberts in forum Excel General
    Replies: 3
    Last Post: 11-15-2010, 04:43 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. How to enter symbols for subset or element of a subset in Excel?
    By rwcita in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2006, 05:30 PM

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