Popup message if duplicate values except if duplicate value is "-" or blank

[atisyam]

Hello, hope that someone could help


What I want to do is that use an Excel formula to trigger a popup if the value entered in a particular column is a duplicate unless if the duplicate value is a "-" or a blank cell. The function would be able to run for the whole column.


For example below: Only F7 will have a popup.


[TABLE="class: grid, width: 497"]
<tbody>[TR]
[TD]1[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1233[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]


I am currently using data validation and I checked the Ignore blank to ensure that if the cell is blank, the data validation would not run.
I chose custom and initially, I wrote: =COUNTIF(F:F,F2=1) Writing this runs for all duplicated values, which is not what I want.


Then I wrote: =(COUNTIF(F:F,F2=1)<>"-")Writing this suddenly stop checking duplicated values. No popup even when there are duplicated values.


Hope that I could get some help. Thank youuu

 

[AlphaFrog]

Try this Data Validation formula...

=OR(F2="-",COUNTIF(F:F,F2)=1)

 

[atisyam]

Try this Data Validation formula...

=OR(F2="-",COUNTIF(F:F,F2)=1)

Ah yes. Great hahaha. Exactly as per intended. I think I confused myself with the <> function. Thank you AlphaFrog