Finding duplicates in a column where the cells hold a formula

Question

I have a workbook with 3 sheets. Sheet "PS", column A contains a list of player names that stays static. Each week, each person in column A sends me a name from an available list. They are allowed to use each name only one time per year. The first name they send I put in column B, the second in column D, the next in column F, etc. Sheet "Data" has each player in a column. I have a formula (=PS!$B$4) etc. in each cell going down to pull the name from sheet "PS" corresponding to that week. I want to easily see duplicates, (which is against the games rules). I can use conditional formatting to change the font color and that does work. I have my Excel set to not show anything in 0 value cells. Because of the formulas, each cell below the entered names has a zero value which makes it a duplicate. I want to highlight the cells that contain a duplicate name. I just can't figure out the formula so I don't highlight all the cells with zeros as well. I'm very new to Excel so I apologize for the misuse of the proper terminology. I can certainly attach this workbook if it would help.

Thank you in advance for your help.

Answer 1

If I'm understanding you correctly then you can just add another condition to your conditional formatting e.g. for highlighting duplicates in column B try this formula

=AND(COUNTIF(B:B,B1)>1,B1<>0)

That will highlight any entry in column B that has a count of more than 1 (i.e. there's a duplicate) but also isn't zero, hence not formatting your zeroes

Answer 2

To my knowledge there is no built in function to determine if a cell contains a formula or a value.

One way to go is to create a simple UDF in VBA to make this determination for you, such as

Function IsFormula(rng As Range) As Variant
    IsFormula = rng.Cells(1, 1).HasFormula
End Function

and use this function in your conditional format