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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!