Project Status to Update a Sheet Summary Status - Formula?

Jenny A.

Is there a way to have a formula in a row or in the sheet summary that does the following:

When a row is added in one sheet and the project status is Completed, then within the project itself in Sheet Summary change the status to Completed as well?

I am not sure if it is possible as one status in one sheet would have to talk to another status in sheet summary in another sheet (and the sheet summaries would all be different as it is project dependent).

Paul Newcome

It should be possible. Are you able to provide some screenshots for context?

Jenny A.

Project Status.png

Status in Sheet Summary.png

@Paul Newcome

I have to be pretty vague, so not sure if the screenshots attached will help. When a project status gets marked as Inactive/Completed the line from the master project list moves to another sheet.

When it moves to the other sheet and has the Project Status of Completed/Inactive on that line, that is when I would want the trigger to somehow go into the project link on the line and change the Status in sheet summary to match the project status that the sheet the project line now lives on, if that makes sense…Thanks!

Paul Newcome

I want to make sure I understand…

You have Sheet A which is a project specific sheet. This sheet has a status in a sheet summary field.

You also have Sheet B which is a list of all Active Projects.

Finally You have Sheet C which is a list of all completed / inactive projects.

You want the status in the summary field of Sheet A to pull the appropriate status based on what is in Sheet C?

Jenny A.

@Paul Newcome Yes you are correct.

When a project is completed/inactive the project status gets changed on Sheet B.

When that happens a workflow moves it to Sheet C.

When it gets moved to Sheet C that is when I want the project status to match the status within the sheet summary on Sheet A, without anyone having to remember to go into the sheet summary on the project and change it manually.

Paul Newcome

You would have to automate all statuses or none of them since we can't have formula and manual entry in a single cell.

Jenny A.

@Paul Newcome

I would be fine with making the status a formula in sheet summary, or at least testing it out. If that would work. Thanks!

Paul Newcome

Ok. In each of the "Master" sheets, do you have something unique on every row that we can use to match on?

Jenny A.

@Paul Newcome if you are talking about sheet B - Active Projects sheet, there is a Project ID column, so when a new project comes in it gets assigned a Project ID.

Paul Newcome

And is that project ID also in Sheet A?

Jenny A.

The specific project ID for said project is in the title of Sheet A.

Paul Newcome

It would need to be in a cell or in another sheet summary field for us to be able to reference it.

Jenny A.

Thanks! I am not sure what formula could go into sheet summary to reference the project ID in a different sheet and pull the appropriate one for said project. Otherwise, that would have to be manual as well.

Paul Newcome

It depends. Are you using Control Center to create new projects?

Jenny A.

New project get submitted via a form and the line goes into one sheet. If approved then the project moves into Sheet B. When that happens the appropriate project person, uses a template to create Sheet A from the approved project line to get the project moving and links the Sheet A project into Sheet B. If that makes sense.

Paul Newcome

Ok. Then in that case yes. Entering the project ID will have to be a manual process.

From there you would use an INDEX/MATCH with an IFERROR along the lines of:

=IFERROR(INDEX({Sheet B Status Column}, MATCH([Project ID]#, {Sheet B Project ID Column}, 0)), INDEX({Sheet C Status Column}, MATCH([Project ID]#, {Sheet C Project ID Column}, 0)))