Return Date (in text column) from a range based on 2 criteria (is not blank, and match by task name)

Hello, I need assistance with following formula. I have a sheet that tracks tasks by type of job. Each type of job is the parent, with children tasks underneath. Each type of job has a few standard tasks that are the same across all job types.

What I am looking to achieve is a formula that will return the Due (or Complete, depending) date if the Start column is NOT BLANK, and that matches Task Names. Basically, I need to ignore all tasks that are for other job types

I have attempted Index(Collect formulas that I pieced together from the community, but cannot find a formula that works.

Ideas?

Tags:

Best Answer

  • Natalia Kataoka
    Natalia Kataoka ✭✭✭✭✭
    Answer ✓

    Hi @Genevieve P. ,

    I am actually only working within 1 sheet, not a source and destination sheet. My final formula came out to:

    =IFERROR(MID(INDEX(COLLECT(Due$14:Due$151, [Task Name]$14:[Task Name]$151, [Task Name]@row, Start$14:Start$151, <>""), 1) + "", 1, 8), "")

    I needed the original date in a date column to fill into a text/number column (so added the +""), but that resulted in both a date + time. I added the MID function to remove the time.

Answers

  • Hi @Natalia Kataoka

    Cross sheet formulas are unable to read hierarchies so you wouldn't be able to filter by the Parent row directly in this formula. What I would suggest doing is add a helper column in your source sheet that brings the Parent Name into each child cell so that you can use it as a filter.

    To do this, you can simply use the PARENT Function, like so:

    =PARENT([Task Name]@row)

    You can make it a Column Formula and then hide the column in your sheet. Once you have this information associated with each child row, then you can add this in your INDEX(COLLECT.

    =INDEX(COLLECT({Due Date Column}, {Helper Parent Column}, "Parent Name", {Task Name Column}, [Task Name]@row, {Start Date Column}, <> ""), 1)


    The <> "" means "not blank".

    Let me know if this is what you were looking to do!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Natalia Kataoka
    Natalia Kataoka ✭✭✭✭✭
    Answer ✓

    Hi @Genevieve P. ,

    I am actually only working within 1 sheet, not a source and destination sheet. My final formula came out to:

    =IFERROR(MID(INDEX(COLLECT(Due$14:Due$151, [Task Name]$14:[Task Name]$151, [Task Name]@row, Start$14:Start$151, <>""), 1) + "", 1, 8), "")

    I needed the original date in a date column to fill into a text/number column (so added the +""), but that resulted in both a date + time. I added the MID function to remove the time.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!