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

Options

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?

Tags:

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    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

    👨🏼💻Dan Palenchar |School of Sheets Solutions Consulting| Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response attention use@Dan Palencharso I get notified of your reply!

  • Stacy Calhoun
    Stacy Calhoun ✭✭✭
    edited 07/02/20
    Options

    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
    Stacy Calhoun ✭✭✭
    edited 07/02/20
    Options
  • Dan Cicciarelli
    edited 07/03/20
    Options

    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
    Options

    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
    edited 07/03/20
    Options

    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
    edited 07/03/20
    Options

    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
    Options

    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
    Stacy Calhoun ✭✭✭
    edited 07/03/20
    Options

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

  • Dan Cicciarelli
    edited 07/03/20
    Options

    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
    Options

    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"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!