excel macro button not working data validation list

Our COUNTIF function returns the count of the value in C3 within the range C3:C7.

The dollar signs “$” are necessary to fix specific cells in our formula and enable the validation criteria to work properly for all selected cells.įigure 5. Enter the following in the Formula bar: =COUNTIF(C$3:C$7,C3)<2 Click the Allow: drop-down button and select Custom as Validation criteria The Data Validation dialog box will pop up. Click the Data tab, then the Data Validation menu and select Data Validation Select the cells whose values we want to restrict. We can do this with Data Validation by following these steps: We want to restrict the Student IDs entered in column C to be unique for each student. Sample data: Data validation unique values only Allow unique values only We can restrict the values entered in column C and allow unique values only by applying Data Validation.įigure 2. We want to ensure that a Student ID is unique to each student. Our table consists of two columns: Name (column B) and Student ID(column C).

criteria – the criteria or condition that determines which cells will be counted.

range – the data range that will be evaluated using the criteria.

Working formula: =COUNTIF(C$3:C$7,C3)<2 Syntax of COUNTIF FunctionĬOUNTIF returns the count or number of values in a specified range based on a given condition Final result: Data validation unique values only This step by step tutorial will assist all levels of Excel users in allowing unique values only.įigure 1. It automatically checks whether the value entered is allowed based on the specified criteria. How to Use Data Validation to Get Unique Values Onlyĭata Validation is a feature in Microsoft Excel which restricts the values or type of data that users enter into a cell.

Excel macro button not working data validation list