Update cell in main row based on values in a group of rows?

jmhoward

Hi there --

I have a sheet that does not use hierarchy, but does have a main row with related rows under it. The main row has a milestone "Primary" and the subsequent rows have Milestone 1, Milestone 2, etc. Within the milestones, there are tasks, such as Task 1, Task 2, etc. I would like the status on the primary milestone line (top line) to update whenever one of the task line statuses change.

For example, if the third task status line changes from TBD- Dependent to In Progress, the primary line status should change to In Progress.

I know we have a different setup, but I have to keep it this way. Is there any way to link one cell to many? Thank you!

Scott Orsey

Hi @jmhoward , That certainly is a unique use case. The solution I suggested before would work better if you were always looking to report the "most advanced" status of all of the children. In your case, however, you would like to report the status of the child that changed most recently. This is a bit trickier. The first challenge is to figure out a way to know when one of the children rows change. Automations are a great way to trigger an action when something changes, however, they only act on the row that triggers them. So you can't change a value in a different row using an automation. I propose two steps:

1) Create a helper column, lets call it [Status Date] that records the last date that the status was changed for each row. To populate this date, you can use an automation that triggers when the status changes and then "records a date" in the helper column. (you could also use the "modified date" system column if you are ok with ANY change, not just the status change, to the row causing the status in your summary row to change ... I don't think this is what you want, but it might be ok)

2) Use a formula to look up the status corresponding to the most recent [Status Date] and return that value for the current LI. There are a few ways to do this. If you aren't that familiar with formulas this will be a bit of trial by fire, because it is not the simplest thing to do. Here is a formula that should work:

= INDEX(COLLECT([Status]:[Status],LI:LI,LI@row),MATCH(MAX(COLLECT([Status Date]:[Status Date],LI:LI,LI@row)),COLLECT([Status Date]:[Status Date],LI:LI,LI@row),0))

To break this down form the inner most formula outward:

COLLECT([Status Date]:[Status Date],LI:LI,LI@row) returns a RANGE that includes all of the [Status Dates] from rows that match your LI

MAX(COLLECT([Status Date]:[Status Date],LI:LI,LI@row)) returns the highest value (most recent date) from that range

MATCH(MAX(COLLECT([Status Date]:[Status Date],LI:LI,LI@row)),COLLECT([Status Date]:[Status Date],LI:LI,LI@row),0) returns the row number (index) within the range you returned of the row with the max [Status Date]. Note the "0" at the end looks for an exact match.

COLLECT([Status]:[Status],LI:LI,LI@row) returns the RANGE of Statuses for all of the rows that match your LI

= INDEX(Collect([Status]:[Status],LI:LI,LI@row),MATCH(MAX(COLLECT([Status Date]:[Status Date],LI:LI,LI@row)),COLLECT([Status Date]:[Status Date],LI:LI,LI@row),0)) returns the Status value from the Status range that matches the index of the maximum date found in the [Status Date] for your current LI.

I hope this is helpful. Be well!

Scott Orsey

Hi @jmhoward , Since you aren't using the dependencies, you will need some way to identify which rows contain the "child" information for each summary row. Since I don't see that in your data that you shared, let's propose that you crate a helper column called Parent ID. In that column, you will need to use conditional statements so that all of the "children" of the summary row have the same ID and that the ID is unique from other summary groups. I'd also create a space on the summary row to show the same Parent ID. Perhaps you can construct the Parent ID programmatically based on the data in the other columns. It's not clear from what you've presented.

Once you have the Parent ID both in the summary row and in the Parent ID column, you can construct a condition in your summary row that will fill in the appropriate Status based on the fields in the children rows. I don't know your logic, so I can only point you to using COLLECT() or DISTINCT(). For example you could have a series of IF conditions that looks like:

= IF(CONTAINS("In Progress",COLLECT(Status:Status,[Parent ID]:{Parent ID],"[Parent ID]@row)),"In Progress", "TBD")

You will need to modify this for your own situation. Hope this helps.

Be well

jmhoward

Thank you, @Scott Orsey. I have a unique identifier for each group, a line-item number, so I can try using that column.

Although I've been using Smartsheet for a few years, I'm very new to formulas, so I appreciate the advice.

My column with the unique ID is the LI column (the parent row and all children rows have the same LI, but nothing else does). I have 15 possible statuses; both the Task Status and Project Status has the same options. When the Task Status changes in any of the child rows, it should update the Project Status to match.

TBD - Not Started

TBD - Dependent

TBD - Banked Funds

TBD - Stalled

Not Started

Z - On Deck

Trafficking

In Progress

At Risk

Delivered

Completed

Stalled

Banked

IO Completed

Cancelled

Scott Orsey

Hi @jmhoward , That certainly is a unique use case. The solution I suggested before would work better if you were always looking to report the "most advanced" status of all of the children. In your case, however, you would like to report the status of the child that changed most recently. This is a bit trickier. The first challenge is to figure out a way to know when one of the children rows change. Automations are a great way to trigger an action when something changes, however, they only act on the row that triggers them. So you can't change a value in a different row using an automation. I propose two steps:

1) Create a helper column, lets call it [Status Date] that records the last date that the status was changed for each row. To populate this date, you can use an automation that triggers when the status changes and then "records a date" in the helper column. (you could also use the "modified date" system column if you are ok with ANY change, not just the status change, to the row causing the status in your summary row to change ... I don't think this is what you want, but it might be ok)

2) Use a formula to look up the status corresponding to the most recent [Status Date] and return that value for the current LI. There are a few ways to do this. If you aren't that familiar with formulas this will be a bit of trial by fire, because it is not the simplest thing to do. Here is a formula that should work:

= INDEX(COLLECT([Status]:[Status],LI:LI,LI@row),MATCH(MAX(COLLECT([Status Date]:[Status Date],LI:LI,LI@row)),COLLECT([Status Date]:[Status Date],LI:LI,LI@row),0))

To break this down form the inner most formula outward:

COLLECT([Status Date]:[Status Date],LI:LI,LI@row) returns a RANGE that includes all of the [Status Dates] from rows that match your LI

MAX(COLLECT([Status Date]:[Status Date],LI:LI,LI@row)) returns the highest value (most recent date) from that range

MATCH(MAX(COLLECT([Status Date]:[Status Date],LI:LI,LI@row)),COLLECT([Status Date]:[Status Date],LI:LI,LI@row),0) returns the row number (index) within the range you returned of the row with the max [Status Date]. Note the "0" at the end looks for an exact match.

COLLECT([Status]:[Status],LI:LI,LI@row) returns the RANGE of Statuses for all of the rows that match your LI

= INDEX(Collect([Status]:[Status],LI:LI,LI@row),MATCH(MAX(COLLECT([Status Date]:[Status Date],LI:LI,LI@row)),COLLECT([Status Date]:[Status Date],LI:LI,LI@row),0)) returns the Status value from the Status range that matches the index of the maximum date found in the [Status Date] for your current LI.

I hope this is helpful. Be well!

jmhoward

Oh wow! Thank you, @Scott Orsey! This is very helpful and informative.

Yes the sheet I'm working on has a very unique setup -- I've never worked on one like it. (The layout is related to data that comes in from outside of Smartsheet.)

I'll try out your suggestions -- definitely a trial by fire. I guess that's one way to get up to speed :)

Scott Orsey

Hi @jmhoward, I hear you! You may want to consider if there is some preprocessing you can do on the data so that you can take more advantage of the SS features. If, for example, you didn't import the summary rows, you could use the reporting features in SS (with grouping and summarizing) to get some of the similar functionality. Though... this need to only see the latest status update is not something that SS would handle intrinsically anyway.

Good luck and be well!