I am looking to show different text results, when there are two or more specific phrases in one column and a date within a certain range in another.
In column C, I have a list of client names.
In column D, cells are labelled either:
In column E, cells include a date in the past in the dd-mmm-yy format. These cells also have conditional formatting.
In column F, I would like to identify whether a client in column C needs:
The criteria I would like to apply is below.
When "1 - urgent focus" and "2 - key focus" are in a D cell and:
When "3 - intermediate focus" is in a D cell and:
When "4 - passive focus" and "5 - not a focus" are in a D cell and:
I hope this makes sense and any advice / ideas would be greatly appreciated.
In column C, I have a list of client names.
In column D, cells are labelled either:
- 1 - urgent focus
- 2 - key focus
- 3 - intermediate focus
- 4 - passive focus
- 5 - not a focus
In column E, cells include a date in the past in the dd-mmm-yy format. These cells also have conditional formatting.
- Red is =TODAY()-$E2>60
- Orange is =AND(TODAY()-$E2>=30, TODAY()-$E2<=60)
- Green is =AND(TODAY()-$E2>=1, TODAY()-$E2<30)
In column F, I would like to identify whether a client in column C needs:
- Urgent attention
- Further attention
- Some attention
- No attention required
The criteria I would like to apply is below.
When "1 - urgent focus" and "2 - key focus" are in a D cell and:
- Where the date is in the red range eg over 60 days ago, column F should read "Urgent attention"
- Where the date is in the orange range eg between 30 and 60 days ago, column F should read "Further attention"
- Where the date is in the green range eg in the last 30 days, column F should read "Some attention"
When "3 - intermediate focus" is in a D cell and:
- Where the date is in the red range eg over 60 days ago, column F should read "Further attention"
- Where the date is in the orange range eg between 30 and 60 days ago, column F should read "Some attention"
- Where the date is in the green range eg in the last 30 days, column F should read "Some attention"
When "4 - passive focus" and "5 - not a focus" are in a D cell and:
- Where the date is in the red range eg over 60 days ago, column F should read "Some attention"
- Where the date is in the orange range eg between 30 and 60 days ago, column F should read "No attention"
- Where the date is in the green range eg in the last 30 days, column F should read "No attention"
I hope this makes sense and any advice / ideas would be greatly appreciated.
