SOLVED

Cell formatting based on other cells content

Copper Contributor

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 ObservedCurrent GradeTLCNext Obervation Due
02/01/2022RED  

 

I'm sure that i have done it before but i can't remember how.

Thanks, Ian

14 Replies
best response confirmed by AutoIan (Copper Contributor)
Solution

@AutoIan 

Let'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

@Hans Vogelaar That's great thanks. It works perfectly. Thank You :)

Thanks for you help with this, can i ask you for some help with my next problem.
i need to add up the months in the next observation column, to show how many are due each month. i have tried but i keep getting errors.
Thanks for your help

@AutoIan 'Can you provide more detailed information about the layout of your worksheet?

Perhaps attach a sample workbook.

@AutoIan I give 2 possible examples in the attached

@Hans Vogelaar i have attached an example of the sheet. Thanks Ian

@AutoIan 

See the attached version. I changed the month names to dates (in 2022), formatted as mmmm to display the full month name.

Hi Hans,
when i extend this for the whole column i get #VALUE!
This is what i have put in the cell - =SUM(--($G$13:$G$90-DAY($G$13:$G$90)+1=D1))
Any ideas? Thanks for your help so far.

@AutoIan 

Could you attach a workbook demonstrating the error?

@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.

@AutoIan 

Uh - there is no Sheet2 in that workbook...

S1047.png

@Hans Vogelaar Sorry sent you the file without saving it first. Schoolboy error lol.

@AutoIan 

Thanks! I see the problem now. I have attached the workbook with a modified formula.

Thanks You Hans, this worked perfectly.
Thank you for all your help!
1 best response

Accepted Solutions
best response confirmed by AutoIan (Copper Contributor)
Solution

@AutoIan 

Let'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

View solution in original post