Need to pull report based on first "No" or BLANK

Jeana

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

Mark Cronk

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

Mark Cronk

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

Jeana

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!

Mark Cronk

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

Jeana

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

Mark Cronk

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

Jeana

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

Mark Cronk

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

Jeana

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

Mark Cronk

Jeana, Glad you got it to work. Happy to help contribute to a solution. Appreciate you accepting my answer and using the Community.

Mark