Last week I showed you how you can extract a list that excludes blank cells for use in a data validation list.
Using this array formula in cells C2:C10:
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:
Entered with CTRL+SHIFT+ENTER as it’s an array formula.
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.
It’d be nice if we could use one of the ways to create dynamic named ranges I showed you a couple of weeks ago, but we can’t
This is because those examples use the COUNTA function, and COUNTA counts blanks returned from formulas.
Which means it would include cells C8:C10 because although they appear blank they actually evaluate to blank using the IFERROR part of the formula.
When a Blank Cell Isn’t Really Blank
We therefore need to use a slightly different formula that excludes blank cells (both real and fake) from the count.
Note: ‘fake blank cell’ is not a technical term :).
Exclude Blanks in a Dynamic Named Range
We can use the following formula to dynamically calculate the named range that excludes both fake blank cells (blanks generated from a formula), and real blank/empty cells:
In English this reads:
Return a range that starts in cell C2 through to the last cell in the range C2:C1000 that isn’t blank.
Which evaluates to:
We can give the formula a name (listIndex) in the name manger:
And use the name as the source for our Data Validation list:
Let’s inspect this formula a little closer:
The starting point of the range is fixed:
The second part is resolved using the INDEX function.
Remember the syntax for INDEX is:
=INDEX(reference, row_num, [column_num], [area_num])
And we’re only using the first two arguments in this formula.
First the SUMPRODUCT part:
Our formula therefore evaluates SUMPRODUCT like this:
Since there are 6 cells in the range C2:C1000 that are not blank.
And since C7 is the 6th in the range C2:C1000 INDEX evaluates like this:
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.
Thanks for sharing, Roberto