Forum Discussion
Excel formula for turning names green if certain criteria are met by a particular date
Hello-
I'm an educator attempting to create a positive parent contact log. I've created columns corresponding to weekly date ranges with dropdown menus for teacher names. The next column includes information about the type of contact (positive or one of 4 negative types). I already have a simple formula to color cells green if it is positive contact and red if it is a negative contact.
Each month, we are required to contact each family with a positive contact at least once. The formula I want to create would highlight the names of students green who have received at least one positive comment in a month. I need to know 1) how to do this for a given month (looking at 4-5 columns of weekly data for each month) and 2) how to do this each month. The idea is to allow the teachers to know by the third week in each month which students' families need to be contacted.
Thanks,
Perrine
Hi Perrine - Thank you so much for letting me use yours. Good teachers borrow from others, so thanks again.
I had the same issue while working on a different spreadsheet for growth tracking (colors not working the way I wanted them to). The trick is to highlight everything that you want to change colors, not just one cell, and create the rule. Or you can go into "conditional formating" and edit the ones you've already created and make it conditional for a range of cells. Hope that is helpful and thanks again!
Hello... I am also an educator and would like your permission to use your idea to track our parent contact. We are using a similar, but slightly more complicate database for tracking our student growth; however, I really like the idea of using it the way you have intended. Thanks!
Feel free! I'm flattered! As I learned many years ago, the best teachers "borrow". I ended up creating different tabs for each month in order to continue the highlighting formula. I also added a "notes" section in the final column for teachers to write any necessary info about the content of the contact. My colleague had suggested adding a column for method of communication, but I was afraid of making the spreadsheet too cumbersome.
I'm having a little glitch now. Only column A changes colors if there is one entry of positive praise. Column B will only change if there are two entries for positive praise. I changed the background color to red instead of having it in red writing, too.
Hello,
it's difficult to suggest formulas without seeing the structure of the data. Could you upload a sample file? I'm also a bit confused about your description. Did you really mean "contact" everywhere you write "contact"? If so, I don't understand where the "comments" come in.
Hello,
Thank you for your reply. Please see my description in the previous reply. When I said "contact", what I mean was calling, emailing, or in some way communicating with a family about a student with positive praise. I've attached the file without the students' names here.
Thank you!
Hi Perrine Punwani,
I just did one CONDITIONAL FORMATING for August/September only.
If any student get "positive praise" in any of first three weeks. Name will turn into Normal.
If i understood wrong. Just explain more. Please see attached file.
Hi Perrine Punwani,
For highlighting use CONDITIONAL FORMATTING.
If you give screenshot of sheet or excel file, we can solve.
Thanks. I've attached the file here.
I'd want the formula to highlight the names in columns A and B red if a family has not been given positive praise about the student by the end of the third week of the "month". For example, for August/September, the names would turn red on 9/15. That would let us know who still needed to be contacted.
Or more simply, I just want to know how to turn the names of families who have been contacted over a 4-5 week period to turn green. I'd want that to reset at the beginning of each 4-5 week period.
Thanks!
