Need to pull report based on first "No" or BLANK
So I have a PARENT/CHILD relationship in this sheet. I need to pull a report for CHILDREN rows that the next task to be done. What would the formula look like that identifies the row highlighted so I can include it in a report? I'm thinking something along the lines of this logic (this isn't the actual formula):
=IF(INDEX(CHILDREN([Helper for Ready to Hand-off]), return the first occurrence of "No", OR return the first BLANK (I could leave the "No" out of that columns formula.
Appreciate any ideas!
Thanks
Best Answer
-
Hi@Jeana ,
I think I have a solution for you:
Add 3 columns to your sheet:[Next [Ancestor], [Next Task] and [Report]
In [Ancestor] add this column formula: =COUNT(ANCESTORS([Deliverables and Tasks]@row)). This will return a number that identifies parent and children. Parent rows will =0.
In [Next Task] add this column formula: =IF(Ancestor@row = 0, INDEX(CHILDREN([Deliverables and Tasks]@row), MATCH("NO", CHILDREN([Helper Ready for Handoff]@row), 0)), ""). This will return the next child task with a No.
In [Report] add this column formula: =IF(Ancestor@row = 1, IF([Deliverables and Tasks]@row = PARENT([Nest Task]@row), "YES", "")) This will flag the child row that you need reported.
Then create a report that contains the rows where [Report] = "yes".
Cross your fingers.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi @Jeana ,
This formula will get you the first Deliverable and Task under a parent that is a "No" in your helper column:
Place this formula in a new column at the parent rows =INDEX(CHILDREN([Deliverables and Tasks]@row), MATCH("No", CHILDREN([Helper for Ready to Hand-off]@row), 0))
Is that what you need?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thanks Mark - I've applied this solution but I'm getting #NO MATCH even when there is a "No" in the Helper for Ready to Hand-off column. I'm still working on it but if you have any ideas I'm all ears.
Thanks!
-
Hi @Jeana ,
Sometimes it's trial and error.
I set up a quick test and found that "Helper for Ready to Hand-off" column needs to have a text/number property. The formula has to be in a Parent row and not in the Helper or Deliverables columns. There has to be a "No" in at least 1 of the children in the [Helper for Ready to Hand-off] column. Change any of those and you get #No Match.
Any luck?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark,
Thanks for your reply. I have it set up as you suggested and on the Parent row for the new column (Task Completed for Calendar) it is returning the value for the first occurrence of "No" correctly. However, I need a report that displays several columns for the ROW where the FIRST "No" shows up in the CHILDREN. Does that make sense?
Thanks for your time on this.
Jeana
-
Jeana, Sounds like a great challenge. Does your sheet have multiple parent rows or just 1? If multiple, do you want a single report to include the next task for all? When you say report, are you looking for an actual smartsheet report or are you looking for an update request or other automation driven action? Back and forth in the Community isn't the efficient but we'll get to a solution. Happy to help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thanks for your reply Mark. To answer your question I'm looking for a Smartsheet report that will show me the information on each row (CHILD) for each Parent (there are several), at the first occurrence of that "No".
I think that if I can come up with a formula to INDEX the CHILDREN and look for the first occurrence in the CHILDREN that has a BLANK in it or the "No" then I could use that formula result to pull the report on. Hope that helps to make it clearer. I thought I had seen in the community people who developed formulas to identify the first BLANK in a column. That's the key I believe.
Jeana
-
Hi@Jeana ,
I think I have a solution for you:
Add 3 columns to your sheet:[Next [Ancestor], [Next Task] and [Report]
In [Ancestor] add this column formula: =COUNT(ANCESTORS([Deliverables and Tasks]@row)). This will return a number that identifies parent and children. Parent rows will =0.
In [Next Task] add this column formula: =IF(Ancestor@row = 0, INDEX(CHILDREN([Deliverables and Tasks]@row), MATCH("NO", CHILDREN([Helper Ready for Handoff]@row), 0)), ""). This will return the next child task with a No.
In [Report] add this column formula: =IF(Ancestor@row = 1, IF([Deliverables and Tasks]@row = PARENT([Nest Task]@row), "YES", "")) This will flag the child row that you need reported.
Then create a report that contains the rows where [Report] = "yes".
Cross your fingers.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
I think with a few tweeks I made I have the answer, THANK YOU!. I already had two of the columns needed for this. It was the REPORT formula that made it finally work. Now I have a column that says YES for the task following the once last complete.
Many thanks!
Here's the formula that worked for me:
=IF([Indent Level]@row = 3, IF([Deliverables and Tasks]@row = PARENT([Next Step Calc]@row), "Yes", ""))
Jeana
-
Jeana, Glad you got it to work. Happy to help contribute to a solution. Appreciate you accepting my answer and using the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!