Index, Match, Parent

Jeff Mac

I'm trying to dynamically change a cell's value depending if a checkbox is checked or not.  Directors of a project would like to know what stage a project is at and if that stage is on target or not.  Where I'm running into trouble is the directors only care about the Parent Task and not the child tasks.  

This is what I have so far but obviously, this does not address the Parent Task only.  

=INDEX(Done:[Task Name], MATCH(false, Done:Done), 2) 

What I would like to have is something like the below but PARENT() doesn't work when the entire column is referenced. 

=INDEX(Done:[Task Name], MATCH(false, PARENT(Done:Done)), 2)

 

Hoping someone can point me in the right direction.

 

Thanks in advance

Andreas S.

Hi Jeff,

Maybe the following helps.

We have tasks (parent) with sub-tasks (children). To give an overview for my boss I put in the columns 

- "Status" which is a red/yellow/gree/grey ball

- "Termin" (translated: date) which is a red/grey flag

- "Report" as a tick-box

- "Percentage Complete" as percent

- "Task" as a tick-box

Then I am doing some automation. 

The "Task" has the following formular:

=IF(COUNT(CHILDREN(Aufgabe@row)) = 0; 1)

So it is set everywhere where are no children, so is a task to do.

"Report" is manual set to each row the boss has to see, mainly the parents.

"Status" has also a formula which does the main work for the status:

=IF(AND(Task@row = 0; Aufgabe@row <> ""); IF(COUNTIF(CHILDREN(Status@row); ="Rot") > 0; "Rot"; IF(COUNTIF(CHILDREN(Status@row); ="Gelb") > 0; "Gelb"; IF(COUNTIF(CHILDREN(Status@row); ="Grün") = COUNT(CHILDREN(Status@row)); "Grün"; "Grau"))); "Grau")

I am setting the ball manually for the tasks, the parent get it automatically set:

- red if there is at least one red child

- yellow if there is at least one yellow child

- green if all children are green

- otherwise grey

"Termin" is fully automated and does the work for dates:

=IF(Task@row = 1; IF(OR(AND(Ende@row <> ""; Ende@row < TODAY(); [%]@row < 1); AND(Ende@row <> ""; Start@row < TODAY(); [%]@row = 0)); 1; 0); IF(COUNTIF(CHILDREN(Termin@row); =1) > 0; 1; 0))

So if it is a task it will be checked for the start and end date

- if start date ("Start") is overdue and is on 0% or

- if done date ("Ende") is overdue and not 100%

If it is a parent and at least one child is red the parent will also be red.

Then last thing to do is a report on all rows where "Report" is set. The red ball then shows my boss anything is totally gone wrong, the yellow there is something on risk, grey not everything is perfect but only minor. The red flag shows him  something it not started but should be or something is not finished but should be.

 

So my main idea is to check for all parents if any of the children has a problem and automtically set this parent to alarm and bring all parents with only children but no parents in a report for my boss.

If you send me your email address I can share a demo with you.

Kind regards

Andreas

 

Paul Newcome

Are you able to provide some screenshots with sensitive/confidential data removed/blocked/replaced with "Dummy data"?

Jeff Mac

Thanks everyone,  I've changed gears a bit and I'm going to approach this in a different way.  

So the question now is if I have a column called Column1 how can I do an index/match which will pick the first value that does not meet specific criteria.  

 

For example, if I have the following cells listed in Column1:

 

No

No

No

(blank)

Yes

 

How would you write a formula which would look through all of Column1 and reference the first cell that is not blank and does not equal "No".

I have something like the below working in Excel but I haven't had any luck with it in Smartsheet.  Note, this only filters out "No" and not the blanks.

=INDEX(A:A, MATCH(TRUE, INDEX(A:A <> "No", 0), 0))

 

 

Paul Newcome

Are you trying to pull data from a different column or the same column?

Is the formula on the same sheet as the data or a different sheet?

Are you able to provide screenshots?

Jeff Mac

I'm pulling data from a different column and everything is located within the same sheet.

 

Screenshot attached.  Column1 has the data and Column2 is where i would like the formula to be located.  In this example the output of the formula should be "Contractor Pre-Qualification"

 

Thanks in advace!

 

 

Paul Newcome

Will there be cells below the grouping that are either blank or contain a "No"?

Jeff Mac

Yes, there will be cells below which will have blanks and possibly "No".  That's  why I'd like it to grab the first cell value that isn't blank or "No" 

Paul Newcome

Ok. That complicates things a little (but only a little). I am going to suggest creating a helper column of the checkbox type.

 

In that column you would use the formula:

 

=IF(COUNTIFS([Column1]$1:[Column1]@row, NOT(OR(ISBLANK(@cell), @cell = "No"))) = 1, 1)

.

When put into row 1 and dragfilled down, it will count down from the top and check the first box that is in the same row as the first cell that is neither blank nor "No".

.

Your formula for pulling the data would be along the lines of this...

 

=INDEX([Column1]:[Column1], MATCH(true, [Helper Column]:[Helper Column], 0))

Jeff Mac

That did it!  Can't thank you enough.  Much appreciated.

Paul Newcome

Happy to help! 

Andreas S.

Hi Paul,

if you send me you email I can share a sheet with you.

 

Andreas

 

Paul Newcome

Andreas,

 

My apologies for the confusion. I was asking the OP if they could post the screenshots. Being able to visualize both the end goal and the issue is always very helpful when looking for a solution.