Using Index/Match/Collect with Cross-Sheet References

Options
Kelly Moore
Kelly Moore ✭✭✭✭✭✭
edited 02/05/20 in Formulas and Functions

Hello All.

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.

Kelly

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Thank you Genevieve!

    I followed you up to your Location reference. Is this my column titled Location (which I have) or is it the table range (or the column range?) for the source data (ie HazMat...). If it is my column named Location I didn't understand it's purpose since I thought DayName was what I was matching to.

    Thank you again for taking the time to help me out, and for your patience as I try to get better at smartsheet.

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Thank you Genevieve!

    I followed you up to your Location reference. Is this my column titled Location (which I have) or is it the table range (or the column range?) for the source data (ie HazMat...). If it is my column named Location I didn't understand it's purpose since I thought DayName was what I was matching to.

    Thank you again for taking the time to help me out, and for your patience as I try to get better at smartsheet.

    Kelly

  • Chris Comer
    Options

    Genevieve- I wish I would have found this hours ago! Thanks so much! Chris

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!