Formula result from another formula
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"
Best Answers
-
=INDEX(CHILDREN([Task Name]2), MATCH("In Progress", CHILDREN(Status2), 0))
Try that.
-
I think I see now...
=INDEX([Task Name]:[Task Name], MAX([WF-Multiplier]:[WF-Multiplier]))
Answers
-
Give this a try and see how it works for you...
=INDEX(CHILDREN([Task Name]@row), MATCH("In Progress", CHILDREN(Status@row), 0))
-
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
-
=INDEX(CHILDREN([Task Name]2), MATCH("In Progress", CHILDREN(Status2), 0))
Try that.
-
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.
-
I think I see now...
=INDEX([Task Name]:[Task Name], MAX([WF-Multiplier]:[WF-Multiplier]))
-
Thanks @Paul Newcome this works :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!