Mar 19 2021 04:11 AM
Hello!
I need some help with a spreadsheet for my client and some conditional formatting.
I've attached a photo of the sheet to show what I'm talking about. I've set it up so when the date in column P is entered then it populates the date 30 days from then in column S, then column V and so on. When the date in column S is is met and up to 7 days overdue then it will highlight orange. When this date is 8 days or more overdue then it highlights red. I cant get the correct formatting to show this so any help would be greatly appreciated.
The next problem which stems from this one is how do I get rid of the red box. It highlights red to show my client that he needs to get in touch with people every 30 days. So if its overdue and highlights red its to remind him to email but when he does it will still be red. Could I add another column in titled 'Sent' and he could add a Y in there when he sends that email. A formula could then be entered to then show if that box has Y then remove the conditional format. Open to ideas if there's an easier way.
Hope that makes sense and thanks very much!
Mar 19 2021 05:27 AM
Hi @KeiraW73
You may use below formula in conditional formatting to achieve your requirements.
I have added a status column , if this column is filled no Red color will be displayed.
A sample file is also attached for your reference.
Please let me know if it works for you.
Thanks
Tauqeer
Mar 19 2021 07:06 AM
This is perfect! Thank you so much for your help! I just couldn't figure out how to combine them both together.
Would you be able to have a look at my sheet and double check my formats for columns J-O. I feel I could condense them like you have but not entirely sure where to start.
It's set up so intro date is added and if it has been more than 30 days the reply column will fill red. If a reply is received and Y added the red will disappear and the next 4 columns show N/A. If a N is entered then the red also disappears and a 2nd intro date is added and so on. There is 3 formats for one cell which seems a lot. Hope that makes sense!
Thank you!!!!
Mar 19 2021 09:14 AM
Hi @KeiraW73
I have tried to modify the logic as per your explanation and that gives below results:
You may refer the attached file.
Thanks
Mar 19 2021 10:33 AM
Mar 19 2021 10:46 AM
Hi @KeiraW73
No issues, you may ask as many questions as required. As far as column H is concerned please use below formula:
=MAX(B5,D5,F5)
You may also refer to the attached file.
Thanks
Mar 19 2021 12:22 PM
Mar 19 2021 12:55 PM
Mar 19 2021 01:03 PM