Simple Way to Remove Blank Cells Dynamic Dropdown List Excel
Whenever I Do a Dependent Dynamic Drop Down List, I See a Bunch of Blank Cell in the Drop-Down List, I Search Many Topics That Explain How to Remove Them While...
Whenever I do a dependent dynamic drop down list , I see a bunch of blank cell in the drop-down list , I search many topics that explain how to remove them while by adding two additional ranges like explained her
but my question is: Is there anyway to avoid blank cell or remove them using a simple approach without the need of two additional ranges or a complex formula?
the drop down list that contains blank cell all I did is go to data validation and wrote in source =MYCode then I named the list that contains the codes like that MyCodeand I checked ignore blank case (even tho It seems to be useless )
4 Answers
There is another way. Create a dynamically-expanding named range. Then use the range to define the data validation list.
To create your dynamically-expanding range, insert this in the named range box and give it a name:
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
$A$1 should be replaced with the top cell of your range. $A$A should be replaced with the column(s) the range is in.
OFFSET points the named range at a range of cells. COUNTA() is in the fourth position of the OFFSET formula, which sets the height of the range. It counts the number of non-blank cells. As a result, when you add a value, the fourth value of the OFFSET formula increases and you get an expanding range.
Note, this does not work if your named range has blank cells interspersed.
OFFSET formula from excel-easy.com.
After some more research I found a solution. In the cell where my information is filled I added a name using the name Manager and I added this formula that I adapted from this article:
=DropList!$J$1:INDEX(DropList!$J$1:$J$10000,SUMPRODUCT(--(DropList!$J$1:$J$10000<>"")))
It did what I needed without the need of adding 2 extra cell, even though the line of code is rather complex.
I know this thread is dead but I found a simpler solution which only requires you to create a single named range. It also works even if blank cells are interspersed in the source data, and expands as the source data expands.
First create your dynamically expanding named range using the formula given above:
=OFFSET($A$1,0,0,COUNTA($A:$A),1)
This will includes the values you want in your drop-down, as well as blank cells. Next, in a separate cell, enter the following:
=FILTER(Your_Range, Your_Range <> "")
The result will be your source data with no blanks. This will spill into adjacent cells. Now can reference this single cell as your data validation list, as long as you add "#" to the end of the cell reference. This tells excel to include all the spilled values in the list.
Benefits:
- If your source data named range is dynamic, the drop-down list will expand with this range, and you don't have to worry about updating a second named range.
- You can easily filter more data, and it doesn't matter if blank cells are interspersed.
- Combined with the SORT and UNIQUE functions, you can further improve how your data is represented in the drop-down list.
I couldn't find a solution this simple anywhere else, so I hope someone finds this useful.
If there are no empty cells within the source list, but the empty rows come from the end of the list, you can get rid of those by using a table as the source of the dropdown. You have to use the INDIRECT function to refer to the table:
- Select the whole source list including the header
- Click
Format as table - Select the table, go to the
Designtab (underTable Tools) - Rename the table
- Select the cells where you want to use the dropdown and open the
Data Validation - As the dropdown source, set:
=INDIRECT("TableName[ColumnName]")(note the double-quotes)
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
