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

H.Colo

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.

Paul Newcome

Is there a reason you are pulling all of the data into the metrics sheets as opposed to using SUMIFS, COUNTIFS, etc. in the metrics sheets with cross sheet references to generate the metrics you are wanting to gather?

H.Colo

Mostly because it made some formulas simpler (to a point, I suppose). For instance, I need to pull what "Denial Explanations" are associated with waiver "SLS", and it was simpler to do that if everything was pulled into the metric sheet. How would I do this with a cross-sheet formula? I modified the one I was using to have cross-sheet references and it threw an INVALID OPERATION ERROR.

=IF(Waiver@row = "SLS", [Denial Explanation]@row) -- This is the local sheet version, but the cross-sheet version --=IF({Waiver} = "SLS", {Denial Explanation}) gives the error.

EDIT:

I'll leave the above for posterity, but the answer was COUNTIFS (duh!). Will revisit with further questions once I have a chance to check all formulas.

Paul Newcome

Correct. COUNTIFS, SUMIFS, etc. should give you the counts you are looking for without having to duplicate all of the data.