# Double Dependent Drop Down + Blank Cells

1. ## 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. ## 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.

3. ## Re: Double Dependent Drop Down + Blank Cells

Please see an example of my spreadsheet

4. ## 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:
`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. ## 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. ## 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:
`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:
`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.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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