Excel data validation list ignore certain values
Smart Dependent Drop-Down Lists in Excel:Expandable & Excluding Blank CellsIn situations where the contents of a drop-down list are frequently changing, its helpful to have a dynamic drop-down list that can automatically expand, as well as exclude any blank cells in the cell references. Show
This example uses the sample data of App names for three divisions: Productivity, Games, and Utility. The drop-down list should display App names based on the Division selected by the user. Adding data validation for DivisionAdd data validation to a selected cell. Go to Data > Data Validation symbol. Under Validation Criteria, select List. For the Source, do a cell reference to the Division names, which are the table headers (cell A4:C4). If set up correctly, it should display as follows: Method 1: Using OFFSET() to create a dynamic drop-down listSetup formula for the data validationWhenever a formula is to be used as a data validation criteria, it is usually easier to first set the formula up on the spreadsheet, then transfer it to the data validation window so that all the Excel helper tips show up as guides. The OFFSET() formula has the syntax: = OFFSET(reference, rows, cols, [height], [width])
MATCH(lookup_value), lookup_array, [match_type])
It now becomes: MATCH($F$4,$A$4:$C$4,0) However, this cant be used as is since the return value of the MATCH() function will be 1, 2, or 3 depending on the selected division. What this means is that when the Productivity division is selected, the MATCH() function returns a 1, which tells the OFFSET() function to move one column to the right instead of staying in column A. To fix this, a correction is made by deducting 1 from the MATCH() function. The col parameter now becomes: MATCH($F$4,$A$4:$C$4,0)-1
COUNTA(value1, [value2], ) However, since the cell reference changes depending on the division selected by the user, another function should be introduced. The OFFSET() function can also be used here. OFFSET(reference, rows, cols, [height], [width])
MATCH($F$4,$A$4:$C$4,0)-1
This now becomes: COUNTA(OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, 20, 1))
The final formula now becomes: =OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, COUNTA(OFFSET($A$4, 1, MATCH($F$4, $A$4:$C$4,0)-1, 20, 1)),1,) A single value will be displayed in the cell. However, the formula actually returns an array and is unable to display all the values on a single cell. To view the entire array, click inside the formula bar and press F9. Press CTRL + Z again to undo. Adding data validation for App namesCopy the entire formula. Add data validation criteria to cell F5 through Data > Data validation. Under validation criteria, select List. Paste the formula to the source text box. Method 2: Using table referencing to create a dynamic drop-down listConvert data table into Excel tablesInstead of converting the entire data table as one Excel table, convert it into 3 Excel tables. To do this, highlight each data column (CTRL + SHIFT + DOWN when selecting the header). Press CTRL + T. Double check if the cell referencing is correct. Tick the box to indicate that the table has headers. Change the table name to the division name. In this example, we use TableProd. Do the same for the Games and Utility columns and rename them to TableGame and TableUtility. Setup formula for data validation criteriaThe INDEX() formula can be used to make sure the correct Table is used as reference. There are two syntax options for INDEX(). The second option will be used. INDEX(reference, row_num, [column_num], [area_num])
Selecting it should now display the table name with the column name in square brackets: TableProd[Productivity]. Select the other 2 columns in the same manner and enclose all 3 in parenthesis. This now becomes: (TableProd[Productivity],TableGame[Games], TableUtility[Utility])
MATCH($F$4,$A$4:$C$4,0) The final formula now becomes: =INDEX((TableProd[Productivity],TableGame[Games],TableUtility[Utility]),,,MATCH($F$4,$A$4:$C$4,0)) Similar to method 1, a single value will be displayed on the cell. However, the formula actually returns an array. To view the entire array, click inside the formula bar and press F9. Adding data validation for App namesCopy the entire formula. Add data validation criteria to cell F5 through Data > Data validation. Under validation criteria, select List. Paste the formula to the source text box. You might receive a message box indicating a problem with the formula. This is because data validation is unable to use this type of table referencing. To remedy this, paste this formula in Name Manager. This is found under Formulas > Name Manager. The Name Manager will pop up and display the three tables. Click on New. Give the formula a new name and paste the formula in the last text box. It should now show up on the list. Go back to Data Validation. Under validation criteria, select List. For the source, write the name of the formula. If you have forgotten the name of the formula you have recently added to Name Manager, press F3. If set up correctly, it should now display the App Names in the dropdown list. Video and WorkbookFeel free to Download the Workbook HERE. Excel Dashboards that Inform & ImpressUse these techniques in your own reportsUnbeatable value!LEARN MORE |