Dynamic List With Blank Cells
If a list contains blank cells, the usual method for creating a dynamic named range doesn’t work. For example, you can use an OFFSET formula, which counts the entries in the column. The count is used to set the number of rows in the range.
When there are blanks, as in the screen shot below, the range is incorrect. There are 9 items, so 9 rows are included, but the last item is in the 12th row of the list.
If you create a drop down list based on this range, it includes blanks, and August is the last month, instead of December.
We’ll fix the problem, to create a list with all the items, and no blank cells.
Number the Non-Blank Cells
To create a drop down list without blanks, you can add formulas to the worksheet.
First, add a formula in cell A2, and copy it down to cell A13. This will number the cells that are not blank.
Create a List Without Blanks
Next, create a list that pulls the numbered items into a new column.
Enter this formula into cell D2, and copy down to D13. This INDEX/MATCH formula creates a list with all the blanks at the end.
Create a Dynamic Range Without Blanks
After you use formulas to create a second list, without the blanks, you can name that range, and base the dynamic range on that.
This range is named ListMonths, and uses the MAX from column A as the row count.
If you create a drop down list based on the ListMonths range, it does not include blanks, and December is the last month.
Download the Sample File
To see the formulas, you can download the file from my Contextures website. On the Sample Excel Files page, go to the Data Validation, and look for DV0063 – Dynamic List With Blank Cells. The zipped file is in xlsx format, and does not contain macros.