Updating Copied Rows
I have automations set up to copy a row from a master sheet to another sheet. But if the master sheet gets updated or changed, the "other" sheet does not. Is there a way that a row in a copied sheet gets updated if the data in master sheet gets changed?
Answers
-
What is the reason for using the copy row automation as opposed to a report?
-
I not very familiar with reports yet. Still learning a lot. My process is this...
We use job costing so each employee has to record their time spent on each job. So I created a "Time Sheet" form that they use to track time per job. The sheet that collects that data from all employees is then imported into QB for payroll.
I have folders and dashboards set up for each employee. I have automations set up so when they submit a time sheet, that row is copied to their individual time sheet. That data is used in their dashboard so they can see how many hours they have put in for the week so the employee can track their weekly time. But if for example, they "clock out" at 4:30 PM but they put 4:30 AM by mistake I have to make that adjustment for the formulas to calculate duration hours correctly. If I correct that on the master sheet, it's not updated on their individual sheet causing their dashboard data to be incorrect.
-
I would suggest creating a report for each employee instead of sheets. You say you are not very familiar with them. Is there a specific part of the process that you have questions about?
-
@Paul Newcome Well, for starters, I created each employee a separate sheet and within that sheet, A summary that calculates how many hours they worked each day of the week, how many hours they are short for the week/how many hours OT they have for the week. That data is used in their dashboard. I just figured out today thought that this process will not work. When copied, rows are copied to the bottom of the sheet, outside of the formula criteria.
I tried creating a report directly from the master sheet and filtered it to a specific employee (myself) but I can't figure out how to "summarize" the data. I can total the hours column for a total for the week but I can't use formulas to calculate how many for each day of the week or how many hours they are short or over 40.
-
As long as you are referencing entire columns, rows added to the bottom of the sheet should be picked up by formulas.
But... I would suggest a report that will simply display the rows as needed. There are a few different ways metrics can be displayed on a dashboard, but here is my personal suggestion...
Create a metrics sheet that will calculate everything for an individual employee. Include a contact type column and put the matching name/email from the master sheet. Make sure all of your formulas reference this cell instead of "hard coding" a name in then link all of your metrics to your dashboard how you want them to be.
Place the dashboard and metrics sheet into the same folder and then save that folder as new. Then you should only have to go into the metrics sheet and update that one cell in the contact column, and all of the formulas should update accordingly (which should also follow through to the dashboard. That should help make replicating the dashboard and metrics for multiple employees relatively quick.
