Change text in one cell based on which cell in a row is completed.

Occasional Visitor

OK, So I am trying to create a tracker for curriculum proposals.  table.png

I want the "Current Status" cell in the row to auto-populate with the column header text based on the one with the most recent date, and I also want the first 4 cells' color to change to correspond with the status.  


So, when I enter the date for received, I want the Current Status to be "received" and the first four to be blue.  Then, when I add the date for the Agenda, I want it to change to "On Agenda," and the color to be yellow, and so forth.


I could probably just add a dropdown and do conditional formatting based on text, but I would like it to auto-populate.  

1 Reply


With this sample


to show the status for the latest date you may use in D3


(drag it down). To pickup color it could be set of conditional formatting rules on column D with formulas like


if you map status numbers on colors. Please check attached.