Nesting If Statements - Looking for Results Across Two Sheets to Pull into a Third

I am working with three sheets for this project: Sheet 1 - Open Projects, Sheet 2 - Finished Projects, Sheet 3 - All Info. When a project moves from "Open" to "Finished", it is moved automatically from sheet 1 to 2 (these are raw data sheets that cannot be changed or combined). I need to pull down the status into Sheet 3 as it is monitoring both. I've changed the column names to try and make it more generic and easier to read.

This is the formula I used with the assumption that, if it can't find it in Sheet 2, it will then look in Sheet 1. What I'm receiving is "Finished" if it shows up on Sheet 2 but, if it is still on Sheet 1, I get #NO MATCH.

=IF(INDEX({Sheet 2 Status}, MATCH(ID@row, {Sheet 2 ID}, 0)) = "Team A Complete", "Finished", IF(INDEX({Sheet 2 Status}, MATCH(ID@row, {Sheet 2 ID}, 0)) = "Team B Complete", "Finished", IF(INDEX({Sheet 1 Status}, MATCH(ID@row, {Sheet 1 ID}, 0)) = "Pending", "Pending", IF(INDEX({Sheet 1 Status}, MATCH(ID@row, {Sheet 1 ID}, 0)) = "In Process", "In Process"))))

Is this not something Smartsheets can do, look for something on one sheet and then switch to a second if not found on the first?

My next step is to then pull in the name of the person who owns it based on the same logic.

Find more posts tagged with

Formulas

Accepted answers

All comments

Lucas Rayala

Hi @Tara F., Smartsheet can handle this, but it may get into the specifics of how you have your data set up. I'm assuming the sheet IDs are specific to the projects. The use of "Team A" and "Team B" is a little confusing -- do they each have a role in the same project? I.e. do they have separate rows on Sheet A with the same project ID?

The way you have your IF/THEN set up now, Team A and B seem to be working on the same project, but if either finishes, then the project is "Finished". If the projects never exist on both pages, however, and any completion is good, then you could do this:

=IF(COUNTIFS({Sheet 2 ID},ID@row)>0, "Finished", INDEX(COLLECT({Sheet 1 Status}, {Sheet 1 ID}, ID@row),1))

The COUNTIFS simply looks for the presence of the project on Sheet 2 -- if it's there, then it's done. The Index/Collect statement collects the status instead of layering additional logic -- it appears you don't care what the status is, you just want to collect it.

If this doesn't work, feel free to send screenshots of your pages to get a better idea of your need. Good luck!

Tara F.

Thank you Lucas. I hadn't thought about using a blank as a way to indicate the project is finished. The COUNTIF worked perfectly!

Lucas Rayala

@Tara F. glad to hear!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.