Excel Magic Trick 916: Remove Blanks In Data Validation Drop Down List 5 Examples
Why Super User recommends it
Super User users link it when someone needs a Data Validation drop-down that skips blank cells, or wants to understand the AGGREGATE and INDEX formulas behind it.
Recommended by 0 people across 0 different months, 2012 to 2015.
What it is
An Excel Magic Trick episode from excelisfun that shows five ways to keep blank cells out of a Data Validation drop-down list. It covers the Ignore Blanks checkbox, deleting blanks with Go To Special, two array formulas (SMALL and AGGREGATE versions), and a dynamic named range built with INDEX and COUNTIF.
What people said
Cites Iszi · 2012-10 · Super User · on “How do I make a Data Validation drop-down exclude blanks?”
Okay, I found a solution but really only have about half a clue how it works. Could someone take a look at this and post an answer that explains it all? [this video]
Cites Spon4ik · 2013-12 · Super User · on “data validation exclude blank from list”
My question is: How can I make a drop-down list that takes dynamic data (dates) and have "blank" cells, <""> because there function that prints date or "" . I've tried this video: [this video] , but didn't work for me because it works only for alphabetical and doesn't work for numeric. Does anyone have any solution?
Cites Clayjoe · 2015-03 · Super User · on “How do I copy multiple rows to another sheet based on selection with checkboxes in Excel?”
@FIXER1234: Update: I ditched the Filter, becuase it had to use macro code and could not be put into a drop-down list. I ended up using this code:=IFERROR(INDEX($E$2:$E$73, AGGREGATE(15,6,(ROW($E$2:$E$73)-ROW($D$2)+1)/($E$2:$E$73<>""),ROWS(I$2:I2))),"") many many times in order to receive the correct information and consolidate it into a nice column where I could make a list, put the info into one column, and show the info on its own sheet without the user needing to know. [this video] this is where I found this code. Thanks again for all your help.