I have a spreadsheet that has a data validation in cells c10 thru c18. User can select either Yes, No or N/A in those cells. In Cell A20 if any of cells c10 thru c18 equal No, I want cell A20 to read Fail highlighted the cell in red with white font. If all of those cells equal Yes, then A20 would read Pass highlighted in green with white font. I am having a terrible time trying to figure this out. TIA!
set conditional formatting in A20 using formula ="Fail" set formatting to red etc...
Then set ANOTHER conditional formatting ="Pass" and set green formatting
if neither condition is met, not formatting will show. (One cell can have more than one conditional formats - as long as there is no conflict)
-also if you highlight the column (beginning with A20 and highlighting through A50 etc..) and yoou set the conditional formatting formula as =$A20="xxx" then the condition will be present in all the cells
Last edited by CRIMEDOG; 12-03-2012 at 11:02 AM.
hi Triscia, use this formula in A20:
=IF(COUNTIF(C10:C18,"no"),"Fail",IF(COUNTIF(C10:C18,"Yes")=9,"Pass",""))
you can then do a conditional formatting on A20 by going to Home -> Conditional Formatting -> Highlight Rules -> Equal to:
Pass
adjust the colour to a custom format you want.
repeat action for "fail"
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
in A20
=IF(CountIF(C10:C18,"No")>0,"Fail","Pass")
Highlight A20, Conditional Formatting >Highlight Cell Rules>Equal To "Pass" with custom format, choose cell color and font color
repeat for the "Fail" condition
Hope this helps
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
A20 keeps showing False instead of Fail. That is weird. I have the formula exactly the same. I also tried the reponse below and I still got a False return.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks