Using Index/Match/Collect with Cross-Sheet References
Daily, four separate audits are performed. The daily audit results are collected via forms (most recent results on top), producing an ever-growing list of results in the respective daily sheets. My goal is to report data from a few columns (named identically on all 4 separate daily sheets) in reports and/or dashboards to management. To help with the filter, I added a checkbox field to the daily sheets to automatically indicate if the data is in the current week (IsWeekNow). Based on your suggestions, I am happy to change my Roll Up sheet and Daily Sheets as needed.
My approach uses Collect to gather the current week data, then use Index/Match to pinpoint the data required for the appropriate day of the week. Ultimately there are 4 fields on the Roll Up sheet to populate from a row of Daily Sheet data.
Using the Index/Match/Collect functions I have yet to land on the right formula. I tried to mimic formulas I found here at the community but I don’t quite understand the syntax. The current formula is yielding an Incorrect argument error. The IssuesFound? column is a 3 symbol column-type that mirrors the 3-symbol type column of the IssuesFound? field on the Daily Sheets. All column types and column names of Roll Up sheet replicate the column type and exact name of the daily sheets. All of the required data, per day, is in a single row on each of the respective daily sheets (newest data on top row).
The formula in a larger font. Each cross-sheet reference range is a single column on the daily sheet:
Here is one of the daily sheets. The highlighted columns are columns that I want the data from. My other daily sheets have these same columns. The data shown is test data
Thanks in advance for your help with my cross-sheet references.
Help Article Resources
Check out the Formula Handbook template!