Jan 06 2022 08:49 AM
I have a tracker that I am using for observations.
I would like to be able to auto populate the next observation box based on the grade of the current observation (Drop down) and the key (Green =+6 months, Amber=+ 3 months, Red = +1 month)
Month Last Observed | Current Grade | TLC | Next Obervation Due |
02/01/2022 | RED |
I'm sure that i have done it before but i can't remember how.
Thanks, Ian
Jan 06 2022 11:00 AM
SolutionLet's say the date last observed is in A2, and the current grade in B2
In D2:
=EDATE(A2,IF(B2="RED",1,IF(B2="AMBER",3,IF(B2="GREEN",6))))
or
=EDATE(A2,IFS(B2="RED",1,B2="AMBER",3,B2="GREEN",6))
This can be filled down
Jan 07 2022 02:38 AM
@Hans Vogelaar That's great thanks. It works perfectly. Thank You :)
Jan 10 2022 09:04 AM
Jan 10 2022 11:02 AM
@AutoIan 'Can you provide more detailed information about the layout of your worksheet?
Perhaps attach a sample workbook.
Jan 10 2022 12:39 PM
@AutoIan I give 2 possible examples in the attached
Jan 11 2022 01:39 AM
@Hans Vogelaar i have attached an example of the sheet. Thanks Ian
Jan 11 2022 05:11 AM
See the attached version. I changed the month names to dates (in 2022), formatted as mmmm to display the full month name.
Jan 11 2022 07:46 AM
Jan 11 2022 08:32 AM
Could you attach a workbook demonstrating the error?
Jan 11 2022 09:57 AM
@Hans Vogelaar Hi Sheet 2 is a copy of the sheet i am trying to input it in to. thanks so much for your help.
Jan 11 2022 01:04 PM
Jan 12 2022 01:05 AM
@Hans Vogelaar Sorry sent you the file without saving it first. Schoolboy error lol.
Jan 12 2022 03:00 AM
Thanks! I see the problem now. I have attached the workbook with a modified formula.
Jan 17 2022 01:01 AM
Jan 06 2022 11:00 AM
SolutionLet's say the date last observed is in A2, and the current grade in B2
In D2:
=EDATE(A2,IF(B2="RED",1,IF(B2="AMBER",3,IF(B2="GREEN",6))))
or
=EDATE(A2,IFS(B2="RED",1,B2="AMBER",3,B2="GREEN",6))
This can be filled down