Time stamp after selecting unit and highlighting based off unit status

Copper Contributor

I am creating a excel sheet (Using excel for web, but it will be downloaded as a editable file using the desktop app) to be used during large scale events for my collegiate ems organization for tracking calls, movement, and status of units. I was wondering if there was a way that when a unit is selected from the drop down list in column D it could autofill a timestamp in the same row in column B.  


Than a second question is there a way to automatically change the fill color of a unit from the list (Found in column H-J) based on the latest "Reference" in column E for that specified unit. For example the latest line for E says "Clear" with the unit in column D being "Tac-1" i would want it to fill row 5 of H-J with a green. 


So if the latest action (reference) of a unit was Clear it would be green. Than if the latest action was Dispatched, On scene, Leaving Reference, or Arrived at Receiving it would be filled red. Than if it was At standby location or Clear Receiving it would fill orange. Than if the reference was status check it is based off the previous reference for that unit. 


I think i made that sound really confusing but i don't know how to word it better. I would be more than happy to answer any clarification questions 


Thank you in advance for any assistance you may offer 

1 Reply

Here is the example