IFERROR, INDEX, MATCH, IF(AND( Formula for 5 sheets?
So, this is a doozy. I am trying to create one report that, when one piece of information is entered, pulls the rest of the necessary information from across five other sheets. I have seen a discussion on this forum about doing just that thing, but the difference is, I need to include certain criteria within the search, which I'm assuming involves some IF(AND( statements, and I'm not sure how to do that across so many sheets.
Essentially, we'll call the main sheet (that has the formula) Report.
The other sheets are Workflow 1, Workflow 2, Archive 1, Archive 2, Archive 3.
So, when I enter a name in Report, I want it to pull the corresponding ID Number and Status from any of the other five sheets, but only if the Program that is related to the name is a certain one.
Is this even possible? Or would it just be easier to create 5 sheets that each pull from one report, and then compile into a report?
Best Answer
-
I understand where you're going - and yes, you could pull from several different sheets in an INDEX MATCH with nested IFs; it'll just be a very long formula. How are you delineating when it should pull from Workflow 1 versus the other 4 sheets (is the criterion you're MATCHing distinct enough to flag 5 different ways?)?
=if(index([answer column 1],match([criterion 1],[reference column 1),
if(index([answer column 2],match([criterion 2],[reference column 2],
if …. ))))) out to the fifth sheet.
Alternate 1. You could make one report that pulls in from those 5 sheets as long as some of the columns line up so it all collates visually how you want it.
Alternate 2. You could also make some helper columns focused on a specific criterion, then use the pulled-in results in subsequent helper columns, if you want to see everything as it pulls. If you're ambitious (which brings us back to the first solution), once you confirm the helper columns work, work backwards to nest their formulas into fewer and fewer helpers.
Answers
-
I see where you're going - and yes, you can nest many IFs, INDEXes, and MATCHes depending on the criteria you're pulling. Is the single criterion (Name?) you'll be looking up per sheet distinct enough to flag 5 different ways?
=if([Program 1], index([answer column 1 in sheet 1],match([Program 1],[reference column 1 in sheet 1]),
if([Program 2], index([answer column 2 in sheet 2],match([Program 2],[reference column 2 in sheet 2]),
if([Program 3], index(… ))))) all in one formula, out to 5 different criteria. It'll just be a very long formula.
Alternate 1. Yes, you can display your 5 sheets in the same report, as long as there is at least 1 common column that will allow the report to collate the info in a way that's visually workable. Helps if the column names are the same too.
Alternate 2. You could create a few helper columns that focus on one criterion at a time, then use the results pulled in from each in subsequent helper columns down the line, if you like seeing it all visually as it pulls. If you're ambitious (which brings us back to my first solution), after you confirm the helper column formula works, you can nest it into the previous one, down the line, until you don't have so many columns.
-
It would have to involve at least one more criteria. So, I would use the Name to match the data needed from the main sheet to the sheets from which I'm pulling the other data, but only if the Program Name associated also matches. Does that make sense?
Here's an example of the formula I'm using now, which is only pulling from one sheet:
=IFERROR(IF(AND(INDEX({Workflow 1 Program Name}, MATCH([Name]@row, {Workflow 1 Name}, 0)) = "Jeff", INDEX({Workflow 1 Assigned Date}, MATCH([Name]@row, {Workflow 1 Name}, 0)) <> ""), INDEX({Workflow 1 Project #}, MATCH([Name]@row, {Workflow 1 Name}, 0)), ""), "")I use a replica of that formula across five different sheets, replacing Workflow 1 with Workflow 2, Archive 1, Archive 2, and Archive 3.
I'm trying to get one formula on one sheet to pull all 5, if possible.
-
I understand where you're going - and yes, you could pull from several different sheets in an INDEX MATCH with nested IFs; it'll just be a very long formula. How are you delineating when it should pull from Workflow 1 versus the other 4 sheets (is the criterion you're MATCHing distinct enough to flag 5 different ways?)?
=if(index([answer column 1],match([criterion 1],[reference column 1),
if(index([answer column 2],match([criterion 2],[reference column 2],
if …. ))))) out to the fifth sheet.
Alternate 1. You could make one report that pulls in from those 5 sheets as long as some of the columns line up so it all collates visually how you want it.
Alternate 2. You could also make some helper columns focused on a specific criterion, then use the pulled-in results in subsequent helper columns, if you want to see everything as it pulls. If you're ambitious (which brings us back to the first solution), once you confirm the helper columns work, work backwards to nest their formulas into fewer and fewer helpers.
-
It hit me as I was reading your response that there may be occasion for one item to appear on more than one sheet at the same time, so I don't think, overall, this would work. We want to see them any time they appear on one of the five, not date-restricted or anything, so I think going with the 5 trackers (which are mirror images of each other) and collating them into one report would be the best bet. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
