+ Reply to Thread
Results 1 to 11 of 11

Filtered drop down list based on another cell value

  1. #1
    Registered User
    Join Date
    03-07-2020
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    12

    Filtered drop down list based on another cell value

    Hello,
    Is there a way to have the drop down list on I5 based on name range 'NumberList" with formula OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A$1:$A$999)-1,1)
    in the attached file to show only items with the value on E =0 ?
    Attached Files Attached Files
    Last edited by Borntobebad; 06-02-2021 at 06:46 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,221

    Re: Filtered drop down list based on another cell value

    Create "Not Delivered" list

    in J1

    =IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,ROW($A$2:$A$8)-1/($E$2:$E$8=""),ROWS($1:1))),"")

    Copy down

    Change "Numberlist" to reference the above

    =OFFSET(Sheet1!$J$1,0,,COUNTA(Sheet1!$J$1:$J$994)-1,1)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-07-2020
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    12

    Re: Filtered drop down list based on another cell value

    Thank you very much!! It does exactly what I need. You are the best.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,221

    Re: Filtered drop down list based on another cell value

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  5. #5
    Registered User
    Join Date
    03-07-2020
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    12

    Re: Filtered drop down list based on another cell value

    John,
    I ran into a problem here.
    I am trying to place the formula you provided into the real spreadsheet, but I can not make it to work.

    The parameters changed a little in the real spreadsheet. The place to look for empty is I instead E and I am placing your formula on Column AD.

    Unfortunately I get blank cells when I do so.
    Please help.
    Attached is the real spreadsheet.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,221

    Re: Filtered drop down list based on another cell value

    =IFERROR(INDEX($A$2:$A$50,AGGREGATE(15,6,ROW($A$2:$A$50)-1/($I$2:$I$50=0),ROWS($1:2))),"")

    Original file had blanks not zero

  7. #7
    Registered User
    Join Date
    03-07-2020
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    12

    Re: Filtered drop down list based on another cell value

    Yeay!!
    It is working.

  8. #8
    Registered User
    Join Date
    03-07-2020
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    12

    Re: Filtered drop down list based on another cell value

    John,
    Everything is working great with the code, but I have another question.
    Is there a way to skip duplicate entries?

    For example:

    If Item 123456 is entered twice by mistake on column a can it be skipped on the formula to only show one?

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,684

    Re: Filtered drop down list based on another cell value

    I was going to suggest setting up another column, say AD, with a formula like =IF(COUNTIFS(A$2:A2,A2)>1,"",A2) so that duplicate values would not be shown.
    The index formula could then be modified to read: =IFERROR(INDEX($AD$2:$AD$50,AGGREGATE(15,6,ROW($A$2:$A$50)-1/($I$2:$I$50=0),ROWS($1:2))),"")
    But it occurs to me that the first instance of the duplicated number might correspond to a value of 1 in column I while the second, which would not be shown, corresponds to zero.
    It might be better to apply conditional formatting to column A that highlights duplicates so that mistakes can be corrected.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Registered User
    Join Date
    03-07-2020
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    12

    Re: Filtered drop down list based on another cell value

    Thank you!

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,684

    Re: Filtered drop down list based on another cell value

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] VBA to Amend the Filter Selection on a Pivot Table Based on a Drop Down List
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-22-2017, 10:00 AM
  2. Drop Down List with Filter and the Filter list in one single column
    By wpm7113 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-28-2016, 06:38 AM
  3. Filter table based on drop down list and show on another sheet
    By ajupillai in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2016, 04:50 PM
  4. Dynamic Drop down list based on a column filter
    By robertfu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2014, 05:57 AM
  5. Changing Pivot Report Filter based on Drop Down List Selection
    By rajuasq in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2012, 11:40 AM
  6. [SOLVED] Macro Filter to hide rows based on Drop Down List
    By dieseldogpi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-12-2012, 11:19 AM
  7. Applying a filter based on a value in a drop down list
    By Melyssa18 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2011, 11:56 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