I need help removing blank entries from an Excel data validation list.
I’ve looked at various solutions, but in my implementation I am using dependent lists to drive several VLOOKUP, so none of the solutions I have found seem to work. As an Excel novice it’s difficult to work out which path I should be heading down, so I’d be grateful to anyone that could help out.
If anyone feels like a challenge and wants to have a look, my sheet can be accessed at: https://www.dropbox.com/s/b7lxe9oagzdaniy/MRF_Dashboard_v0.6.1.1.xlsm?dl=a
For your New list, in Raw!DL2 use this array formula.
=IF(LEN(DL1),IFERROR(INDEX($A$2:$A$99, MATCH(0,IF($A$2:$A$99<>"",IF($DK$2:$DK$99="",COUNTIF(DL$1:DL1,$A$2:$A$99),1),1),0)),""),"")
Array formulas require Ctrl+Alt+Delete to finalize. Once entered correctly you can fill down to Raw!DL100. This array formula produces a list of the numbers from column A where column DK is blank.
Similarly, the array formula for Raw!DM2 would be:
=IF(LEN(DM1),IFERROR(INDEX($A$2:$A$999, MATCH(0,IF($A$2:$A$999<>"",IF($DK$2:$DK$999="Pending",COUNTIF(DM$1:DM1,$A$2:$A$999),1),1),0)),""),"")
Fill down as necessary.