Forum Discussion
Conditional Formatting multi rule help!
Hi all,
I've asked a very similar question to this before, but the helpful answers I got previously now don't seem to work for my spreadsheet. Please could someone talk me through how do this:
Below is a spreadsheet we use to tell us when a patients prescription has come into the clinic. We manually put this data in. I need a set of rules as follows if possible:
If there is a date in the 'Date of Injection appointment' cell (in this case F13) and there isn't anything in the 'Prescription received in clinic' cell (in this case O13) then I would need the patient name cell (in this case D13) to turn a different colour depending how close the date of injection is. Does that make any sense?!
So if the date of injection (F13) is less than 4 days from today and cell O13 is empty it needs to be filled red, if cell F13 is less than 6 days from today filled yellow and if cell F13 is more than 8 days from today then filled white.
I'd really appreciate someone's help please. I'm losing my mind! Thank you in advance
3 Replies
You want conditional formatting on the Patient Name column (D) based on the Injection Date (F) and Prescription Received (O).
Highlight all the cells under D (Patient Name) where you want the formatting applied.
Go to:
Home → Conditional Formatting → New Rule → Use a formula to determine which cells to formatThen enter the following formulas one by one (adjust row numbers if your data starts somewhere else):
Red (Injection within 4 days, no prescription)
Formula:
=AND($F13<>"",$O13="", $F13-TODAY()<=4)
Yellow (Injection within 6 days, no prescription)
Formula:
=AND($F13<>"",$O13="", $F13-TODAY()<=6, $F13-TODAY()>4)
White (Injection more than 8 days away, no prescription)
Formula:
=AND($F13<>"",$O13="", $F13-TODAY()>8)
Make sure the rules are listed in this order (Red first, then Yellow, then White).
Tick “Stop if true” for each so Excel doesn’t apply multiple colors at once.*Inserted file with various conditional formatting examples.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Thank you NikolinoDE that worked! yay!
One more piece of help if you wouldn't mind? Could you also tell me a formula for if the patient is booked in for their injection and the prescription has been received into the clinic so that the name turns green? Thank you so much
For the green rule, you want the Patient Name cell (column D) to turn green if:
There is a date in the injection column (F), and
There is a value in the prescription received column (O).
Formula for Green:
=AND($F13<>"",$O13<>"")
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
