Populate multiple metric sheets with cross-sheet formula(s)

H.Colo ✭✭
edited 08/10/22 in Formulas and Functions

I'm looking to improve how I've set up several metric sheets to make sure data is staying up-to-date. The components of the workflow are here:

  1. Sheet 1 = This is the reference sheet that is used in reporting. Our billing department uses it to track claims. It's a basic sheet with a simple automation that moves rows to an archive "Completed" sheet once they've processed a claim.
  2. Metric Sheets = 4 sheets that track various types of metrics, such as current FY, past FY, month-by-month, and overall statistics. I need to get data from Sheet 1 to these sheets in a way that always has up-to-date information, as these sheets feed dashboards that the team uses to make decisions. 

I am running into a problem with the cross-sheet formula I've set up to get data from Sheet 1 into the Metric Sheets. I am using an INDEX + MATCH function that numbers rows from 1 to whatever number is needed, shown in the image below of one of the Metric sheets.

The formula is this: =INDEX({Status}, MATCH([Row Index Match]@row, {Row Index Match}, 0)) where {Status} is the desired value from Sheet 1, [Row Index Match] is the numbered column on the left in the image, and {Row Index Match} is a corresponding column on Sheet 1, which would ideally always be numbered in increments of 1 no matter how many rows were moved or changed.

These index numbers are intended to match row numbers on Sheet 1 to ensure the data in the metric sheets (and therefore the data surfacing on the dashboards) is comprehensive and always current. However, I can't get the row numbers to work on Sheet 1, because the automation means that rows are constantly moving. This keeps breaking the sequence the INDEX + MATCH function relies on, so it keeps breaking reporting.

I have two questions:

  1. Is there a better way to use cross-sheet formulas to get the data into the Metric Sheets from Sheet 1?
  2. If the INDEX + MATCH route is the best way to go, how can I ensure all rows are accounted for even if rows are constantly moving from Sheet 1? Every method I've found to make a sequential index column on Sheet 1 (autonumber or using a formula) breaks when rows are moved.

Thank you very much for taking the time to look at this question. Please let me know if there's any additional information I can provide.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!