Using Index/Match/Collect with Cross-Sheet References

Kelly Moore

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

Genevieve P.

Hi Kelly,

I think the reason you're getting an error is because you're trying to use multiple criteria with an Index(Match... it also looks like you're asking the formula to return a whole range of cells if there's an error, instead of one value.

You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell, assuming that only one value meets all the criteria).

The way JOIN(COLLECT works is that you first list the range that has the value you want returned (your IssuesFound column in the source sheet), and then list each range and criteria afterwards. I've added in the additional criteria that the location has to be HazMatWhse, in case you had multiple locations in one sheet.

Try this:

=JOIN(COLLECT({Issues Found Range}, {Is Week Now Range}, 1, {Day Name Range}, DayName@row, {Location Range}, Location@row))

I also didn't add an IFERROR around the formula, as this will just be blank if there are no matches. If you want to add an IFERROR, decide what you want the value returned to be... for example, if you want it to say "Not Found", try this:

=IFERROR(JOIN(COLLECT({Issues Found Range}, {Is Week Now Range}, 1, {Day Name Range}, DayName@row, {Location Range}, Location@row)), "Not Found")

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function

Let me know if this works for you!

Cheers,

Genevieve

Kelly Moore

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

Genevieve P.

Hi Kelly,

No problem at all! Here, I'll break it out a little more for you... I'll call your second image the source sheet and the first image, the one where you put the formula, the current sheet. You are absolutely correct in that we are matching the DayName, but I was adding an additional criteria to compare between sheets:

=JOIN(COLLECT({Issues Found Range}, {Is Week Now Range}, 1, {Day Name Range}, DayName@row, {Location Range}, Location@row))

{Issues Found Range}

Column that you want the value returned. In the source sheet, it's called "IssuesFound?"

{Is Week Now Range}, 1,

Looking in the source sheet, at the column called "IsWeekNow", to see if the box is checked (indicated by "1")

{Day Name Range}, DayName@row

Looking in the source sheet, at the column called "DayName" to make sure that it matches what's in that row in your current sheet, in your current sheet's column "DayName"

{Location Range}, Location@row

Looking in the source sheet at the column called "Location", to make sure that it matches what's in that row in your current sheet, in the current sheet's column "Location".

Does that help clarify? Anything in {these} is referencing a whole column in the source sheet. Anything in [these] or without brackets but with an @row, is referencing a cell in a column in your current sheet.

Let me know if I can help further!

Cheers,

Genevieve

Genevieve P.

Hi Kelly,

I think the reason you're getting an error is because you're trying to use multiple criteria with an Index(Match... it also looks like you're asking the formula to return a whole range of cells if there's an error, instead of one value.

You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell, assuming that only one value meets all the criteria).

The way JOIN(COLLECT works is that you first list the range that has the value you want returned (your IssuesFound column in the source sheet), and then list each range and criteria afterwards. I've added in the additional criteria that the location has to be HazMatWhse, in case you had multiple locations in one sheet.

Try this:

=JOIN(COLLECT({Issues Found Range}, {Is Week Now Range}, 1, {Day Name Range}, DayName@row, {Location Range}, Location@row))

I also didn't add an IFERROR around the formula, as this will just be blank if there are no matches. If you want to add an IFERROR, decide what you want the value returned to be... for example, if you want it to say "Not Found", try this:

=IFERROR(JOIN(COLLECT({Issues Found Range}, {Is Week Now Range}, 1, {Day Name Range}, DayName@row, {Location Range}, Location@row)), "Not Found")

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function

Let me know if this works for you!

Cheers,

Genevieve

Kelly Moore

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

Genevieve P.

Hi Kelly,

No problem at all! Here, I'll break it out a little more for you... I'll call your second image the source sheet and the first image, the one where you put the formula, the current sheet. You are absolutely correct in that we are matching the DayName, but I was adding an additional criteria to compare between sheets:

=JOIN(COLLECT({Issues Found Range}, {Is Week Now Range}, 1, {Day Name Range}, DayName@row, {Location Range}, Location@row))

{Issues Found Range}

Column that you want the value returned. In the source sheet, it's called "IssuesFound?"

{Is Week Now Range}, 1,

Looking in the source sheet, at the column called "IsWeekNow", to see if the box is checked (indicated by "1")

{Day Name Range}, DayName@row

Looking in the source sheet, at the column called "DayName" to make sure that it matches what's in that row in your current sheet, in your current sheet's column "DayName"

{Location Range}, Location@row

Looking in the source sheet at the column called "Location", to make sure that it matches what's in that row in your current sheet, in the current sheet's column "Location".

Does that help clarify? Anything in {these} is referencing a whole column in the source sheet. Anything in [these] or without brackets but with an @row, is referencing a cell in a column in your current sheet.

Let me know if I can help further!

Cheers,

Genevieve

Chris Comer

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

Genevieve P.

@Chris Comer haha happy to help!