Updating "master" task sheet status from six sub-project task sheets

I want to keep task sheets for six projects. I want them to feed to a "master" sheet. I have successfully created the automations to copy tasks as added to the six sheets to the master. Where I am struggling is in updating the master as tasks move from "new" to "in process" to "complete". I don't want to use an automation because I do not want to add duplicate rows. I just want to update the rows. I can use vlookup but it can only look at one sheet. I need a formula to look at six sheets and find the matching task so I can update the status. I have successfully used IFERROR to create a formula that looks at two sheets:

=IFERROR((VLOOKUP([Task Name]1, {Project1 Tasks Range 1}, 2, false)), (VLOOKUP([Task Name]1, {Project2 Tasks Range 1}, 2, false)))

This formula looks for the task on Project1. If it doesn't find it, it generates an error and looks at Project2. If it finds the task on Project2, it updates the status. I tried to nest it again to look at a third sheet, but I cannot get it to work. Has anyone tried anything like this? Is there a better way to parse multiple sheets? Thank you in advance.

Find more posts tagged with

Formulas

Project Management

Accepted answers

Dale Murphy

@GrantKJohnson What is the purpose of the master sheet, other than as a central task creation sheet?

Could you create the sub-project sheets separately to start, and use a report that consolidates a view across all the (sub-) project sheets?

If there is value in creating tasks in a central sheet and copying these rows into sub-sheets, you could maintain that part of the process and still have a report to produce the aggregation. (Maybe moving the rows is better, to avoid duplication?)

You can update status in that report instead of directly in the master.

All comments

Dale Murphy

@GrantKJohnson What is the purpose of the master sheet, other than as a central task creation sheet?

Could you create the sub-project sheets separately to start, and use a report that consolidates a view across all the (sub-) project sheets?

You can update status in that report instead of directly in the master.

GrantKJohnson

@Dale Murphy : Thank you. I knew there had to be a better way. No reason besides I am a Smartsheets newbie and still learning. I will try your way. It sounds much better!

Dale Murphy

@GrantKJohnson Great. Yes, I discovered the reporting functionality that pulls several sheets (or entire workspaces) into a single report is useful. Mind you, it requires careful sheet construction, ideally from the same template, so that columns have matching characteristics.

Cheers,

GrantKJohnson

Thank you again. I did create all my sheets from the same template. It works perfectly!

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.