Copy Row Automation

kelceyg

Hello!

I have been tasked by my team's leadership to create a way that they can audit how often my colleagues are updating their project plans.

We currently have a "Master Sheet" that lists all of our current projects and certain cells linked in from each project plan. The project manager is supposed to be updating their executive summary weekly. There is a column in the project plan called "Exec Summary Update" where the PM manually changes the date to the date they've updated their summary. This cell is then linked to the project plan.

I thought it could build an automation to copy the row to a new "Audit of Master Sheet" sheet whenever the "Exec Summary Update" column is changed.

I ran my test, but nothing copied over to the Audit sheet. I scrolled to the bottom and nothing is there. I suppose my first question would be if we're even able to trigger an automation off of a linked cell?

If so - what any idea on what is going wrong?

If not - any suggestions on a better way to do this?

Note: the goal of the audit is for the leadership team to be able to see every time a person updated their executive summary. For example: if I updated my "Audit Testing" project's executive summary today, and then again tomorrow, the Audit sheet would show my project in two rows with each date. Then leadership could go into the Audit sheet, filter by my name, and see that I updated my exec summary twice.

Any thoughts or suggestions are most welcome! Thanks in advance!

Katy H

Unfortunately, I have learned from experience that you cannot trigger automation based on changes in a linked cell. This sheet looks like a "portfolio rollup" sheet? Aka, a collection of cell links looking at all active projects? If so, I think it would require building out the automation at the project level where the changes are made, creating a sheet that documents the changes, and then create a report for each project to allow the auditors to track those changes.

Unfortunately, this would mean either making the change to each and every project manually, or changing the project template and re-provisioning the updated template to the teams.

I did this for a employee training documentation program I built, each employee gets a template set and the template set includes a sheet where documented trainings are copied over each time the employee fills out a date for a training.

Wish I had an easier solution! Perhaps someone else knows how to manage this, but I am happy to talk it through if you would like to dig deeper.

Paul Newcome

You can set up a workflow to run on a weekly basis. If the last update is supposed to be in by 5:00pm Friday, then you can set it up to run at 6:00pm Friday.

You can create a flag that has a formula to indicate which dates are more than 7 days in the past to show which were not updated in the past week.

kelceyg

@Katy H - I thought about moving the automation to the project sheets, but that would not work as I only need the row where they are marking the date of the Exec Summary Update copied over, but it ends up copying over the entire project plan.

I tried adding a column to my Master Sheet with the formula "=[Exec Summary Update]@row" with the thought that it would update everything the linked cell updates, but be an unlinked cell that could trigger the workflow. However, it has not worked.

I think I will end up having to do what @Paul Newcome suggested and run a weekly workflow and copy the entire sheet each week.

Katy H

@kelceyg I think that is your best bet! Hopefully it works out for you!

Paul Newcome

If you were willing to put in the work to add it to every project plan, you could try inserting a hidden checkbox column and manually check the box for that single row then include that box being checked as one of the conditions.