Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.