I am making a excel sheet where I have a dropdown list (just a data validation list) containing the status of a project. I would like to fill multiple cells with the date that one of the drop option is clicked. So for example when the status is changed from Ongoing to Finished, the date in the column "Date finished" is automatically filled with the current date and time.
I currently have the following formula in the cells that need to get filled in automatically:
=IF(ISNUMBER(SEARCH("Ongoing", A2)), NOW())
Using this formula the date does not "freeze" in the cell and when you choose another status the date is removed from on cell and updated to another one.
What would be a straightforward approach to this problem? Thanks in advance.