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?

Find more posts tagged with

Formulas

Accepted answers

Natalia Kataoka

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.

All comments

Genevieve P.

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

Natalia Kataoka

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.