Aside: Roberto from Excel blog E90E50 pointed out to me that we can actually leave out the “” from the SMALL(IF part of the formula as the SMALL function doesn’t consider the Boolean values. So we can shorten the formula to this:

OK moving on, now that we have a list in column C without blanks we want to use it as the Source of our Data Validation List, and we want to allow for growth in the list, which means it needs to be dynamic.

Download the workbook

Note: this is a .xlsx file, please ensure your browser doesn’t change the file extension when you download it.

Bonus: See sheet ‘r’ in this workbook for an alternative solution provided by Roberto that hides errors using conditional formatting instead of IFERROR, and therefore uses a different formula for the named range used in the data validation list.

Hi,
Can you please upload a sample workbook with your data structure on our Help Desk? It will be a lot easier for us to understand your situation and to assist you in this problem. Any detail you can give is important: you need to include the “0″ but ignore the blanks where? In a data validation list?
I’ll wait for the file
Cheers,
Catalin

Hi,
I’m just starting to understand this and it’s great but why can’t I get it to work using both text and numbers in my list? I’ve tried variations, ISBLANK, or(istext,isnumber). I just don’t know enough about how excel works. Any help would be appreciated

This method works perfectly for lists which are just manually entered values. However, I have a list which is populated based on the result of a formula [that formula being =IF(L9=TRUE,C9,"")], and I am trying to remove the blanks that result in this list from my drop down list. They are “false blanks” as you named them above, and I am having trouble modifying your formula to suit. Currently, the new list generated has the same blanks in the same spots.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

100 Excel Tips & Tricks eBook - FREE

Almost There

Please enter your email address and click the button below to get instant access to the Excel Tips & Tricks.