How Do I Return a Status/Name From a Different Sheet within a Project in Sheet Summary

Jenny A.

Within each project we utilize Sheet Summary to pull into reporting and ultimately dashboards.

There are a couple fields that I would like not to be manual, but instead use some kind of formula if I can. The issue is that the formula would have to pull the name/status from a different sheet (I noticed there is a reference option under sheet summary in formulas and if possible both formulas I need help with would use this).

So for example and I am not sure how to do this formula (and if the reference should be the whole column, etc), but:

First Question:

Needs to pull from a different sheet.

IF - Project Channel = Airline, then the name that should populate should come from the Culinary Column. The issue is that there may be multiple projects under the same channel, so maybe capturing the first instance of it would be good.

The Second Question:

Needs to pull from a different sheet.

IF Project ID = XXXXX, then return the value that is in Project Phase Column.

Paul Newcome

I think it is a missed parenthesis. Try this:

=IF(COUNTIFS(Status:Status, @cell = "On Hold", Ancestors:Ancestors, @cell = 0) > 0, "On Hold", IFERROR(LEFT(INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0)), FIND("-", INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0))) - 2), "Complete"))

Paul Newcome

You could try this:

=IF(COUNTIFS(Status:Status, @cell = "On Hold", Ancestors:Ancestors, @cell = 0) > 0, "On Hold", IFERROR(LEFT(INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell = "In Progress", Ancestors:Ancestors, @cell = 0), 1), FIND("-", INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell = "In Progress", Ancestors:Ancestors, @cell = 0), 1)) - 2), "Complete"))

Paul Newcome

Are you able to provide some screenshots for context?

Jenny A.

First image is within the project in sheet summary and there is an option when doing a formula to Reference Another Sheet.

Second screenshot I cannot show the rest of the sheet, but I need to essentially reference certain columns or #'s/names within the columns depending. Not sure if this helps.

Paul Newcome

Are you able to do a quick mock-up of the reference sheet that has mock data in it?

Jenny A.

If I should attach something somehow or link something let me know, but I just mocked it up. Most of the columns would be filled out/have different data, etc. This is basically the sheet that I would want to pull data from into the individual projects in sheet summary.

Paul Newcome

Ok. And I see you are wanting to use sheet summary fields. Are these fields part of the same sheet as the data or a different sheet?

Jenny A.

Each individual project has a certain set of sheet summary fields and the active project list (from my example above) has a different set of sheet summary fields. I am hoping to pull certain fields from the active project list (using some kind of formula) into each individual project through sheet summary fields and then in turn pull those fields from each individual project into a report for a dashboard. Does that help?

If there is an easier way to go about this, I will try that.

Paul Newcome

How about this… Can you mock up a manual version of the expected outcomes so we can see what we are trying to automate?

Jenny A.

If there is an easier way to pull from within the project itself I would rather do that. These fields would otherwise, be very manual and then people would have to remember to update them.

1. The project itself has different phases. If there can be a formula that auto updates the project phase in sheet summary that would be helpful. Like when one project phase gets marked as Complete under Status and then next one is marked as In Progress then Project Phase gets updated in sheet summary.
   1. Example - All would default to Concept as the starting phase.
      1. Once Concept is marked complete or not required, and Development is marked in progress, then the phase would change to Development in sheet summary.
      2. There would maybe have to be another option or part of the formula where if a phase gets marked as On Hold under Status, then the phase in sheet summary would change to On Hold.

Let's start with this one. The other one, I kind of have an idea on and will test some things. If I am able to get some kind of starting formula, I should be able to fill in the rest of the project phases, etc. Hopefully this helps! Thanks

Paul Newcome

First start by inserting a text/number column (called "Ancestors" in this example) an use this column formula:

=COUNT(ANCESTORS([Task Name]@row))

Then to get the "in Progress" phase, we would use something along the lines of

=INDEX(COLLECT([Task Name]:[Task Name], Ancestors:Ancestors, @cell = 0, Status:Status, @cell = "In Progress"), 1)

Jenny A.

Thanks! I can get it to work. Is there a way to add to the formula and remove the parts after the dash in the name. So instead of pulling Concept - Phase 1, it would just pull Concept and so on? And also if a Phase gets Marked as On Hold in the Status, we would want that to show up instead of Concept or Development, etc. Not sure if that is possible. That is the only other thing that could happen.

Thanks

Paul Newcome

So basically you want the first one that is not "Complete". What if all are complete?

Jenny A.

If all Phases are marked Complete then it would not be active anymore and would not be pulled for the report this goes into.

Sometimes a project goes On Hold. So if the Status column is changed to On Hold (instead of In progress), then that would over-ride the project phase it is currently in. If possible.

Also, if multiple phases are in progress (which could happen), then if possible the Phase that is not in the first position would be the phase that pulls into sheet summary. Ex. sometimes the Concept Phase will be in progress the same time as the Development phase, but we would want the Dev phase to pull on the report.

I am not sure if both of these items are possible. Thanks

Paul Newcome

Ok. Give this a try:

=IF(COUNTIFS(Status:Status, @cell = "On Hold", Ancestors:Ancestors, @cell = 0) > 0, "On Hold", IFERROR(LEFT(INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0)), FIND("-", INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0), COUNTIFS(Status:Status, @cell <> "Complete", Ancestors:Ancestors, @cell = 0)) - 2), "Complete")

Jenny A.

Thanks! I copied and pasted and it gave me this error.

Paul Newcome

The Ancestors column uses the COUNT formula from above. That last formula is the one that outputs the status in your sheet summary field.