How to Preserve INDEX Formula Data When Archiving Row?

Question

The Issue

My project-specific metadata sheets use INDEX/MATCH formulas that reference the Intake sheet using a Project ID. When the automation archives rows from the Intake sheet, these formulas break because they can no longer locate the source data.

Example formula:

=INDEX({Project Intake Name}, MATCH([Project ID 1]@row, {Project Intake Project ID}, 0))

This causes me to lose all the linked metadata information in the archived projects.

What I Need

I'm looking for solutions to preserve the metadata when rows are archived. Since we have the Enterprise Smartsheet plan, I'm open to:

* Converting INDEX formulas to static values once a project closes
* Automation workflows or other features that could help maintain data integrity
* Any other approach that keeps the metadata accessible after archival

Paul Newcome · Answer

You can update your INDEX/MATCH with an IFERROR to look at the archive sheet if there is no match on the active sheet.