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

[juleem]

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

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.  

[#2]

@juleem 

With this sample

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

=IFERROR(INDEX($E$1:$L$1,MATCH(LARGE($E3:$L3,1),$E3:$L3,0)),"")

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

=MATCH($D3,$E$1:$L$1,0)=6

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