Trying to pull data into a cell from one column based on criteria from another column

Stacy Calhoun

Hello everyone,

I've built a project schedule and I'm trying to pull only the task that's "In Progress" into the parent row of that phase.

So basically my formula needs to look at the children in the status row, find the one that states "In Progress" and pull the data in the Task column from that same row into another cell.

Any idea on the formula to achieve this step?

SoS | Dan Palenchar

Can you share a screenshot of the Sheet in question? It's not clear to me exactly what you want the parent row to show

Stacy Calhoun

Here's a screenshot of my sheet. What I'm trying to do is input a formula that says: If a status is marked as "In Progress", then I pull the information from the Project name column, within that same row.

For ex: A formula to pull the task (Define scope definition..." that's currently "In Progress" into another cell.

Stacy Calhoun

Dan Cicciarelli

Hi Stacy,

I just wanted to clarify your requirement here. From what I understand you are looking to report in the parent line "Big Data BoK Development the status "In Progress" if any of the 4 tasks have an "In Progress" status set in the "Status" column? is the trigger only having at least one task in the child group as "In Progress"?

Here is what you can do.

=IF(COUNTIF(CHILDREN(Status22), @cell = "In Progress") > 0, "In Progress", "")

This Formula can be added to the "Status" of the parent task in your screenshot (No need for a new column).

The Formula does a check of the cells using the "@cell" function to check the string of text of the children and returns a total count. If the count is greater than zero it will return the string "In Progress" If the results is 0 it will return "blank".

Hope this helps.

If you want you could expand the formula with some nested IF statements to provide a "Not started" or even "Completed" status based on the child tasks.

Cheers,

Dan

Stacy Calhoun

So I want to pull the name of that actual task that’s “In Progress” into the parent line. So in the screenshot I would be pulling “Define scope definition & determine the role(s) involves in the field” into the parent line and not the status wording “In Progress”. If there is not task that in progress then I just want the cell to be blank. What I’m essentially doing with that parent row is using that information in a dashboard which is why I’m looking to pull the task that’s currently in progress into the parent line.

Dan Cicciarelli

Hi Stacy,

you can then just return the task name in the same formula I proivded like this.

=IF(COUNTIF(CHILDREN(Status22), @cell = "In Progress") > 0, [Task Name]22, "")

Where Status22 = the column name and row you are referencing and [Project Name]22 is the row you want to return

Dan Cicciarelli

Wait I have misread your feedback.

So what if there is multiple statuses that are "In Progress" are you expecting them to reflect in the "Current Status" column with the information from the "Project Name" column? e.g. 1:1 relationship?

If so you could use this formula and drag it down which will only return values that are in progress.

=IF(Status24 = "In Progress", [Project Name]24, "")

Or are you looking to have a 1:M and concatonate the values into a single parent cell?

Stacy Calhoun

Hi Dan,

For now, there is only one task under the "Project Name" column that's "In Progress", however, if there ends up being multiple then I would like to concatenate the values into a single parent cell. Is that possible?

Stacy Calhoun

So when I put in the formula, it shows up blank with nothing in the parent cell

Dan Cicciarelli

Hi Stacy,

Can you send a screenshot through of what you have tried and also include the row numbers in the screenshot?

Can you also paste the formula in the comments so I can see what you have done?

Cheers,

Stacy Calhoun

Ok so I used the following formula and it gave me what I want: =(INDEX(CHILDREN([Project Name]@row), MATCH("In Progress", CHILDREN(Status@row), 0)))

However, how can I revise this formula to include those "Not Started"? There are some children rows that have a "Not Started" status instead of an "In Progress"