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?
Best 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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!