Using Index/Match/Collect with Cross-Sheet References
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Genevieve- I wish I would have found this hours ago! Thanks so much! Chris
-
@Chris Comer haha happy to help!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!