Formula result from another formula

Roger

Hello,

I need some help :)

I need to calculate the [Taskl Name] and the [Status] from the next "milestone" in progress, and put those values in a header of the sheet.

To determine the next "milestone" in status "in progress" i use the following formula, in every "milestone" row, on column: [WF-Multiplier]

[WF-Multiplier]=COUNTIFS([Task Name]$1:[Task Name]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))) * IF(Status@row = "In progress", 1, 0)

(tanks @Paul Newcome i found the first part of the formula in another post)

This will return the rownumber if the status is in progress, otherwise a 0

-------

Now i go to the header

I would like to do something like "=[Task Name]XX"

where XX is calculated by:

=MAX([WF-Multiplier]$1:[WF-Multiplier]$51)

I dont know how to "concatenate" this to make it work

I tried things like

[Taskl Name]MAX([WF-Multiplier]$1:[WF-Multiplier]$51)

[Taskl Name]$MAX([WF-Multiplier]$1:[WF-Multiplier]$51)

[Taskl Name]+MAX([WF-Multiplier]$1:[WF-Multiplier]$51)

all result Unparseable

Thanks in advance

(in the screenshot, the result should be)

=[Task Name]17 --> "PR"

Paul Newcome

=INDEX(CHILDREN([Task Name]2), MATCH("In Progress", CHILDREN(Status2), 0))

Try that.

Paul Newcome

I think I see now...

=INDEX([Task Name]:[Task Name], MAX([WF-Multiplier]:[WF-Multiplier]))

Paul Newcome

Give this a try and see how it works for you...

=INDEX(CHILDREN([Task Name]@row), MATCH("In Progress", CHILDREN(Status@row), 0))

Roger

This stops at line 2.

But its a good approach.

I can delete the status of line 2, or somehow start checking from line 3

Paul Newcome

=INDEX(CHILDREN([Task Name]2), MATCH("In Progress", CHILDREN(Status2), 0))

Try that.

Roger

Thanks again, the problem with this is that will stop @first Task is status "in progress" and not of of the tasks "milestones" i have selected to be candidate to to "checked" (blue lines).

In my example, all the tasks in the Milestone Quality Checks are "In progress", same as milestone, once all are complete the milestone can be "Completed".

During this process the Parent project will show the Milestone, name, not the Milestone Quality Checks as it does with your last formula.

Once the milestone is "completed" the next set of QC and its corresponding Milestone (in this example TFA) are set to In progress and then the formula should result as "TFA".

Im not sure if im explaining correctly.

Paul Newcome

I think I see now...

=INDEX([Task Name]:[Task Name], MAX([WF-Multiplier]:[WF-Multiplier]))

Roger

Thanks @Paul Newcome this works :)

Paul Newcome

Happy to help. 👍️