Identifying Current working Task and it's Parent

falcnor

Good day!

I've been reviewing quite a number of ways in finding the current task, last completed cell in a column, etc etc and cannot get what I need to work.  

I've attached screenshot with the data in my sheet and I'll provide the formulas below.

What I'm after is looking for the last non-blank cell in the Status column, capturing that value and it's Parent's Task Name column value.

I thought I might need the Parent Task Name in the same row as the cell I'm looking at but that doesn't seem to help either.

Formula in first row of column Current Step:

=INDEX(ParentName:Status, COUNT(Status:Status) + 3, 1)

Formula in first row of column Current Task:

=INDEX(ParentName:Status, COUNT(Status:Status) + 3, 2)

 

Any and all help would be GREATLY appreciated.

Paul Newcome

So you are looking to display (according to your screenshot provided) "Analysis" as the Current Step and "Analysis Task 1" as the Current Task?

falcnor

That is correct.

Sorry for the delayed reply, it seems I'm not getting notifications of replies.

Paul Newcome

No worries. If "Not Started" will only be displayed once and that is the task you are trying to pull, you would use something like this:

.

In Current Step:

=INDEX(ParentName:ParentName, MATCH("Not Started", Status:Status, 0))

.

In Current Task:

=INDEX([Task Name]:[Task Name], MATCH("Not Started", Status:Status, 0))

falcnor

No, I need the last filled in cell in that column then the task and step information from that row.

It can be any number of Status values.

Paul Newcome

And you want to ignore the parent rows that are blank in the Status Column?

Paul Newcome

Lets see if this works for you...

 

Add in a helper column. I'll call it "Helper" for this. In the Helper column, enter the following:

 

=IF(ISTEXT(Status@row), COUNT([Task Name]$1:[Task Name]@row))

 

Dragfill this on down the column.

.

In Current Step:

=INDEX(ParentName:ParentName, MAX(Helper:Helper))

.

In Current Task:

=INDEX([Task Name]:[Task Name], MAX(Helper:Helper))

falcnor

Current Step is the parent name for the task. Inputing what you suggested gives a blank for Current Step cell.

Current Task is showing the Parent of the task instead of the task itself.

 

Paul Newcome

That's my fault. I didn't take into account the blank row at the top.

 

New formula in helper column:

 

=IF(ISTEXT(Status@row), COUNT([Task Name]$1:[Task Name]@row)) + 1

falcnor

That did 'er!

Thanks so much Paul!

Paul Newcome

Awesome! Happy to help.