Record a Date Based on Index Match Updating

James Tontarski

I have a project request sheet that I want to record the dates when the row moves to the next status. I do not want to create additional columns in the original sheet. What I have done so far is create an automation that when a new row is created, it will copy it over to a Helper sheet.

On the Helper sheet, I have an Index Match set up that pulls in the Status for each row. I have 4 statuses I want to record: Submitted, Under Review, Review Meeting, Accepted. I have created 4 columns with Date formatting.

I thought it would be as simple as making an Automation to Record a Date when the Index Match column updates to one of the statuses, but nothing is actually being recorded.

Any ideas for how to best record these dates?

Paul Newcome

Automations cannot be triggered from cells containing cross sheet references. The best we can do is a regular check on a set frequency and update that way. Your automation would be date based and you would set it to run daily at a specific time of day. Your two conditions would be checking the status and where the date field to be updated is blank (assuming you have separate date columns for each status).

You could set it up to run as frequently as hourly by setting up multiple daily automations set to run at different times.

Paul Newcome

You could also leverage the API, the premium add-on Bridge, or some other third party app.

James Tontarski

Thank you for the answer, Paul. It's a shame that cross sheet references can't provide this functionality. However, I was able to get the data recorded by creating a DataMesh between the two sheets to update the Helper sheet's Status column.

Paul Newcome

I don't use DataMesh very often, so it regularly slips my mind as an option. Glad you had the thought!