Creating Archive Files In Smartsheet

Jim Hook

While rewriting my 2016 Smartsheet application that tracks all sorts of weekly and monthly data on projects and employee billable hours charges, I needed to be able to store those results in archive files for use by various sheets and reports that use that data. Automated workflows allow that data to be copied to another sheet, or archived, but if there were changes to a weekly archive record I needed to be able to archive the data again but enable other sheets and reports to ignore previous records for the same time period. Since Smartsheet does not allow automated workflows to modify cells in other sheets, or write over a pre-existing row, I came up with the following process that solved my problem. It works as follows:

1. An automated workflow, typically triggered from a Smartsheet report, copies the desired rows to multiple archive sheets during my weekly processing.
2. The archive sheet includes a column that assigns each newly copied row a record number. This happens automatically as new rows are copied to the archive sheet.
3. I add a second column to the archive sheet that identifies if the record number is the last instance of the desired date/project, date/employee or date/employee/project in the archive. Again, Smartsheet automatically adds the formula that does this to all newly added rows.
4. I add a third column to the archive sheet that assigns "Yes" if the record is the last record for the desired data and a "-" otherwise. This column could easily be combined with the column above but I find it helpful to do it this way for troubleshooting purposes. Again, Smartsheet automatically adds the formula that does this to all newly added rows.

Then, when any other sheet or report fetches records from the archive sheet it typically uses a SUMIFS() or INDEX(MATCH) function along with the "Yes" from Step 4 above to get only the final record for that combination of criteria. Never use VLOOKUP() functions to fetch data from the archive files since doing so could result in the requesting sheet exceeding the 25 million limit to external cell references. I've used this approach to archived data many times now with great results.