# Formula result from another formula

✭✭
edited 05/08/20

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

all result Unparseable

(in the screenshot, the result should be)

Tags:

• ✭✭✭✭✭✭

I think I see now...

• ✭✭✭✭✭✭

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

• ✭✭
edited 05/08/20

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...

• ✭✭

Thanks @Paul Newcome this works :)

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!