+ Reply to Thread
Results 1 to 6 of 6

Double Dependent Drop Down + Blank Cells

  1. #1
    Registered User
    Join Date
    04-30-2020
    Location
    Kansas City, Missouri
    MS-Off Ver
    2016
    Posts
    3

    Double Dependent Drop Down + Blank Cells

    All,

    I've been stuck on this problem for a few days now.

    I am building a sheet for work that needs three drop downs, the first selects the row title (of which many are repeated), the second selects the column header and the third gives you the the options in the table below. Please see my formula below.

    =OFFSET('Lookups-Master Events'!B3,MATCH($C10,'Lookups-Master Events'!$B$4:$B$64,0),MATCH($D10,'Lookups-Master Events'!$C$3:$H$3,0),COUNTIFS('Lookups-Master Events'!$B$4:$B$64,C10))

    I get the right amount of options but a lot of times there are blanks in the data and the drop down can show 10 options and 10 blanks (which show up as 0 if you paste the formula into excel vs the data validation page).

    I need a way to try and remove these blanks from the drop down data.

    Please let me know if you need any more information

    Thank you!

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,149

    Re: Double Dependent Drop Down + Blank Cells

    It may help someone to better understand what you are trying to accomplish if you would attach a sample workbook that illustrates the issue.
    Instructions for upload are given in the banner at the top of the page.
    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.

  3. #3
    Registered User
    Join Date
    04-30-2020
    Location
    Kansas City, Missouri
    MS-Off Ver
    2016
    Posts
    3

    Re: Double Dependent Drop Down + Blank Cells

    Please see an example of my spreadsheet

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,149

    Re: Double Dependent Drop Down + Blank Cells

    This is a low tech proposal.
    Based on the drop downs in D18:E18, L3:L16 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    M3:M16 are populated similarly based on the drop downs in D19:E19
    L17 is populated using: =SUMPRODUCT(--(L3:L15<>""))
    M17 uses a similar formula.
    Data validation for cell G18 has a source of: =OFFSET($L$3,0,0,L16)
    The data validation for cell G19 is similar.
    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    04-30-2020
    Location
    Kansas City, Missouri
    MS-Off Ver
    2016
    Posts
    3

    Re: Double Dependent Drop Down + Blank Cells

    I have a couple if thats alright?

    So i did a poor job of explaining I think, this is the dummy sheet. The actual sheet i need for my company is 1000's of lines long and is located on a different sheet. So the two green cells that go to the yellow cell will have 1000 options and needs to be dragable do to needing to add lines. So while your answer is correct i do not believe it could work on a grander scale? Basically my trials out to the right can not be used to try and find the right answer.

    I am going to write a couple of notes on the sheet to try and explain better.

    Thank you for your help.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,149

    Re: Double Dependent Drop Down + Blank Cells

    In the attached file the input (Lookups-Master) and output are on separate sheets.
    On the input sheet the formula for the "Cleaned up list from table headers for building drop-down" formula is modified to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The modification allows the list to expand as new column headers are added to the table. Note that both highlighted lists may need to be moved depending on the number of columns anticipated.
    On the output sheet the following formula produces the list for the dropdown in the yellow cells:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the list produced by the formula is horizontal so it may be dragged down the sheet.
    The source for data validation in the yellow cells is: =OFFSET($J2:$O2,,,,COUNTIF($J2:$O2,"?*"))
    The source for the drop downs in column D is: =OFFSET('Lookups-Master'!$U$5,,,COUNTIF('Lookups-Master'!$U$4:$U$14,"?*")-1)
    The reference of the OFFSET function is set to cell U5 to exclude the word Category from the list.
    Let us know if you have any questions.

+ 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. Creating drop down with MATCH and dependent cells
    By Bipin Jha in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-22-2018, 11:06 AM
  2. [SOLVED] multiple dependent double drop-down menus
    By Wietse21 in forum Excel General
    Replies: 3
    Last Post: 02-15-2018, 06:38 AM
  3. Starting Dependent Drop Down List with Blank
    By marcelkahn5 in forum Excel General
    Replies: 3
    Last Post: 08-02-2015, 01:39 PM
  4. Replies: 1
    Last Post: 05-21-2014, 08:11 AM
  5. [SOLVED] Removing blank cells from a dependent drop list in a single column
    By Dbeethekidd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-04-2013, 04:15 AM
  6. [SOLVED] Creating cells dependent on a drop-down list
    By FraserP in forum Excel General
    Replies: 3
    Last Post: 05-11-2012, 01:47 PM
  7. [SOLVED] Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)
    By Waqar Ali in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-26-2012, 06:31 AM

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