Cell formatting based on other cells content

Occasional 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


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

Thanks, Ian

14 Replies
best response confirmed by AutoIan (Occasional Contributor)


Let's say the date last observed is in A2, and the current grade in B2

In D2:




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


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.


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.


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


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


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!