Alert users about repeating values in a column

[doritos5991]

Hello everyone,

I want to flag columns that have repeating numbers for our employee schedule. In column J for example, 1001 is repeating to show who is covering for an employee who is off that day. I want a message to appear in excel that alerts the user that 1001 or any number in the column has been repeated. The message should let the user know to change that number to another value or write OFF. Thanks for the help!

[mikeava]

Please see attached file. It is called Data Validation.

Click on Cell J9. On the Data tab - click Data Validation,
Under Settings you will see the formula I have written. Go to Error Alert and you can reword what I wrote in that box.

Let me know if you have any quesitons.

[doritos5991]

This is exactly what I needed! I have two follow up questions.

1. Could I select all the columns I want to apply this to at one time or would I need to go one column at a time?

2. When I try to type the word OFF it flags it when I only want it to flag repeated values. Is there a way to allow duplicated words or text and only flag numbers?

[Bo_Ry]

Please try Custom Data validation at J9:W15

=(J9>"")+NOT(COUNTIFS(J$9:$J$20,J9)>1)