Automate Copy/Paste Special in same sheet or to another

We have "live" metrics collecting data from multiple departmental sheets, and display them on a dashboard. Works beautifully. The challenge is, we need to save each metric into an historical sheet, which means we need to store the numbers as static numbers. Copy/Paste Special accomplishes this — but it has to be done manually. If there was an automation for this, it would solve the dilemma of manually capturing each metric at the right moment, and copying/pasting "special" by hand.

Sign in to vote!

Find more posts tagged with

Formulas

Automations

Idea Submitted

Comments

Erin Horiuchi Green

@Pauline J It sounds like your looking to create an Audit Log that captures every new record and changes.

I have a Smartsheet…call it…"Information Origin" that is where information is entered and updated based on what fields of information are unlocked for collaborators. Since this is the origination point, I have an Index column, Created By, Created Date, Modified By and Modified Date.

I use the dedicated out-of-the-box Automation Workflow to copy every new record into a separate Smartsheet …call it… Audit Log. In addition, the Audit Log has an additional checkbox column "Original" and "Change" to differentiate between the original and updated records.

An out-of-the-box automation workflow copies New Records into the Smartsheet that serves as the Audit Log and also checkmarks the field "Original".

Then I have a subsequent automation workflow copies any Existing Record where targeted (or any) field change was executed and checkmarks the field "Change".

From the Smartsheet that is serving as the Audit Log, I have different Reports that are pulled in as Metrics on a Dashboard depicting:

1) Request Submission Total Volume by Created By

2) Request Submissions This Week (requires an additional column and formula in "Information Origin") by Created By

3) Change Request Total Volume by Modified By

4) Change Request Volume This Week (utilize same column from #2 above) by Modified By

More can be extracted depending on what story you are trying to paint or uncover.

Pauline J

@Erin Horiuchi Green Thanks so much for your response. I am not trying to create an audit log; I need to copy a value that is the result of a formula and paste it into the same (or it could even be another sheet) using paste/special so that the result is the actual number, as if it were typed by hand. Instead it copies the formula. That won't work for capturing the metrics' histories, and the metrics change often.

I could not find any work around — it looks like this will need to be done manually until the paste/special is an option through an automation. If you have any other ideas, I'm certainly open! 🙂

Erin Horiuchi Green

@Pauline J Then create a separate Smartsheet where each cell is a cross sheet reference of the original Smartsheet and make the cross sheet reference the column formula.

Pauline J

@Erin Horiuchi Green I appreciate your input — however, I don't see cross-sheet referencing will solve the issue? The challenge is that the result of the formula must be stored as a whole number, disconnected from the original formula. Otherwise, the result will change every month, as the formula re-calculates. I hope that makes sense — if there is a way to do that, it would be great!

DanMan

Very much would appreciate the functionality requested by Pauline. Have been trying to solve this exact issue for quite some while now. In my case it would improve the user experience substantially and mitigate manual errors

Pauline J

@DanMan — I don't know if this will help, but my work-around is to have formulas that replicate the monthly "final" metric using 3 columns in Row 1. (see below; I did not include the actual columns with the metrics in the screenshots, but I think it should make sense). Then, on the last day of the month, each metric sheet has an automation that copies Row 1 into an historical sheet — I only care about the first few columns in the row, but it doesn't matter that the rest of the columns are icluded in history.) I built my charts on the dashboard to pull the data from the historical sheet, which has the values themselves, not the formulas..

It's working fairly well — but I do check on it often just to be sure it's running properly. This is Row 1 from the original sheet, base on formulas:

Each month that row copies to the historical sheet, and adds the current values of Row 1 as the next entry in the sheet:

Quick Links

Trending ideas

Resource Management Plan Settings in Control Center - add Client and Project Code fields
In the Resource Management Plan Settings in Control Center, there is no way to link the Client and Project Code fields even though they are not Custom fields and part of the first 3 fields you enter when creating a new project. I can't select them in the Custom Resource Management Information Fields because they are not…
WorkApp Analytics
I’d like to see user analytics for WorkApps. I’ve checked the user report available in the Collaborator Pack, but it only shows how many times someone has clicked into the WorkApp each month. I’d like to know: Which resources (dashboards, sheets, forms, etc.) are getting the most traffic? Where are people spending their…
Sheet Icon Color Consistency
The 'Sheet' icon appears purple in the left navigation but blue within the sheet, creating inconsistency. Aligning icon colors across the platform would improve clarity and simplify user training. Please standardize the icon color to enhance user experience and reduce confusion during onboarding.