• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Format Calc vs. In cell Calc

B

barmacost

New Member
Has anyone ever encountered this situation...


I'm using Countifs formula with named ranges to compare two lists (on separate tabs) to highlight when cells do not match. Generally speaking it works correctly but there are certain instances where the lists match perfectly but the cell is still being highlighted and when I check the formula in a cell it correctly shows that the items do match and should not be highlighted so I'm not sure why the formula works in a regular cell but not when applied as a conditional format.


Has anyone ever encountered this situation and what did you do to fix it?
 
Not sure how to post an example but this is the formula I'm using. Like I said it works 90% of the time but there are certain cells that even though they have identical "Priorities" come up as TRUE for conditional formatting but FALSE as a formula in a cell.


=COUNTIFS(CRID,$A4,Priority,"<>"&$N4)=1
 
Barmacost I had this exact problem a few days back XL 2003 in a post on this forum -


=AND(WEEKDAY(A5)=7,ISEVEN(WEEKNUM(A5)))


returns TRUE no worries in a cell but you can't enter it to CF - error msg is something about you can't use references to other worksheets or workbooks which I am clearly not.


No answer to this one except use a Helper Cell and have a simpler CF formula basing itself on this Helper Cell.
 
You must log in or register to reply here.
Back
Top