Learn how to create a dynamic drop-down list in Excel that skips blank cells and automatically updates as you add or remove items from the source list.
Drop-down lists are useful features in Excel that allow you to limit the choices for data entry in a cell. You can create a drop-down list from a range of cells, a table, or a named range. However, sometimes you may want to create a dynamic drop-down list that skips blank cells and automatically updates as you change the source list. In this article, we will show you how to do that using a combination of formulas and data validation.
Table of Contents
Creating a Source List with No Blanks
The first step to create a dynamic drop-down list is to create a source list that has no blanks. For example, suppose you have a list of items in column D, and some criteria in column E that may exclude some items from the drop-down list. You can use the following formula in column F to create a new list that only shows the items that meet the criteria:
=IF(E3>0,D3,"")This formula checks if the value in column E is greater than zero, and if so, returns the corresponding item in column D. Otherwise, it returns an empty string (“”). You can copy this formula down to the rest of the rows in column F.
However, this will result in a list that has some blank cells, which we don’t want to include in the drop-down list. To remove the blanks, we need to use another formula in column G that will return only the non-blank cells from column F. The formula is:
=IFERROR(INDEX(F$3:F$10,SMALL(IF(ISTEXT(F$3:F$10),ROW(F$3:F$10)-ROW(F$3)+1),ROW()-2)),"")This is an array formula, which means you need to enter it with Ctrl+Shift+Enter. This formula uses the INDEX function to return the value in column F at a specific position, which is determined by the SMALL function. The SMALL function returns the k-th smallest value from an array, which is the result of the IF function. The IF function checks if the cell in column F is text, and if so, returns its row number minus the row number of the first cell in column F plus one. This way, we get an array of numbers that correspond to the positions of the non-blank cells in column F. The k argument of the SMALL function is the row number of the current cell minus two, which starts from one and increments by one for each row. The IFERROR function handles any errors that may occur when there are no more non-blank cells to return, and returns an empty string instead.
The result of this formula is a list of items in column G that has no blanks and matches the criteria in column E. You can copy this formula down to the rest of the rows in column G.
Creating a Dynamic Named Range
The next step is to create a named range that refers to the list in column G, but dynamically adjusts to the number of items in the list. To do this, go to the Formulas tab on the Ribbon, and click on Name Manager. Then click on New, and give a name to the range, such as “ItemList”. In the Refers to box, enter the following formula:
=OFFSET(Sheet1!$G$3,0,0,COUNTA(Sheet1!$G$3:$G$10)-COUNTBLANK(Sheet1!$G$3:$G$10),1)This formula uses the OFFSET function to return a reference to a range that starts from cell G3, has zero rows and columns offset, and has a height and width that are determined by the COUNTA and COUNTBLANK functions. The COUNTA function counts the number of non-empty cells in the range G3:G10, and the COUNTBLANK function counts the number of blank cells in the same range. By subtracting the latter from the former, we get the number of non-blank cells in the range, which is the height of the named range. The width is one, since we only have one column. Click OK to create the named range.
Creating a Drop-Down List from the Named Range
The final step is to create a drop-down list from the named range that we created. To do this, select the cell where you want the drop-down list to appear, and go to the Data tab on the Ribbon. Click on Data Validation, and choose List from the Allow drop-down menu. In the Source box, enter the name of the named range, preceded by an equal sign, such as “=ItemList”. Click OK to create the drop-down list.
You can now see that the drop-down list shows only the items that meet the criteria in column E, and skips any blank cells. Moreover, if you change the values in column E, or add or remove items from column D, the drop-down list will automatically update to reflect the changes.
Frequently Asked Questions (FAQs)
Question: How do I create a drop-down list in Excel?
Answer: To create a drop-down list in Excel, you can use the Data Validation feature. Select the cell where you want the drop-down list to appear, and go to the Data tab on the Ribbon. Click on Data Validation, and choose List from the Allow drop-down menu. In the Source box, enter the range of cells, the table, or the named range that contains the items for the drop-down list. Click OK to create the drop-down list.
Question: How do I create a dependent drop-down list in Excel?
Answer: To create a dependent drop-down list in Excel, you need to create multiple named ranges that correspond to the different options in the first drop-down list. For example, if you have a drop-down list of countries, and you want to create a dependent drop-down list of cities, you need to create a named range for each country that contains the cities in that country. Then, you can use the INDIRECT function to refer to the named range that matches the selection in the first drop-down list. For example, if the first drop-down list is in cell A1, and the named ranges are named after the countries, you can enter the following formula in the Source box of the second drop-down list:
=INDIRECT(A1)This way, the second drop-down list will show the cities that belong to the country selected in the first drop-down list.
Question: How do I create a drop-down list with checkboxes in Excel?
Answer: There is no built-in way to create a drop-down list with checkboxes in Excel. However, you can use a workaround that involves using a form control or an ActiveX control. You can insert a checkbox control from the Developer tab on the Ribbon, and link it to a cell that will store its value. Then, you can use conditional formatting to show or hide the checkbox based on the value in the cell that has the drop-down list. You can also use VBA code to automate the process of creating and deleting the checkboxes.
Summary
In this article, we learned how to create a dynamic drop-down list in Excel that skips blank cells and automatically updates as you change the source list. We used a combination of formulas and data validation to achieve this. We also answered some frequently asked questions related to drop-down lists in Excel. We hope you found this article helpful and informative.
Disclaimer: This article is for educational purposes only and does not constitute professional advice. The information and examples are based on the author’s personal experience and research, and may not apply to your specific situation. You should always consult a qualified expert before making any decisions or taking any actions related to the topics discussed in this article.