Automate Copy/Paste Special in same sheet or to another

Pauline J

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.

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: