Pulling data from predecessor row into current task row

Karen_Pytel

Hi everyone! I am hoping that one of your brilliant minds can help me with this. I am working to set up Smartsheet transmittals at my organization and there is one sticking point that I'm trying to solve for.

Our sheets have notifications set to send once a predecessor row, or rows are marked complete. The predecessor row will contain information in a [Links] column that includes files to be worked on or handed off. The trouble is, the notification for the current task only sends that row's data in the notification email, not data from the predecessor row. I'm looking for a formula that could do one of the following:

1) Pull data from the [Links] column in the predecessor row(s) into a [Transmitted Links] column in the current task row

or

2) Identify the [Next Task Assignee] by pulling the [Contact Lead] contact from the predecessor row into the current task row, which would then enable me to create a current user report that contains all transmitted files sent within the last 5 days.

If there is a better way that you can see, I am completely open to suggestions!

Thank you for your help and consideration. This community continues to be my life preserver in the sea of complex formulas!

L_123

I've used index(ancestors()) several times for this with a helper column

=if(count(ancestors())>0,index(ancestors(),1),"")

Then set it as a column formula

You can use this same strategy for 1 and 2.

Karen_Pytel

@L_123 Thank you for responding! The rows will not always have a parent/child relationship, and instead, the formula should look at the predecessor column to identify which predecessor rows relate to the current task row to pull the data in. Will that formula still work in this case? What other helper column might be needed here?

L_123

What do you mean by predecessor? the row immediately above the analyzed row?

Karen_Pytel

In this case, predecessors are used in context of a schedule. The column "Predecessors" identify which rows a task row is dependent on before it can begin. In the screenshot below you can see that row 19 is dependent on row 15 finishing (marked 100% in the "% Complete" column) before it can start. The assignee on row 15 would not only mark their task complete, but also include transmitted file links and notes in that row (columns not pictured). The assignee in row 19 would receive a notification that their task was in progress, however, any links or notes would not appear in their task row data (pulled into the notification message), because they actually live in row 15.

I need a formula that can reference the processors column and pull select column data down into dependent task rows.

L_123

Do you have an autonumber column?

L_123

If you have an autonumber column you can use a join(collect(match()))

=join(collect(Links:Links,predecessors:predecessors,@cell = match(autonumber@row,autonumber:autonumber)),char(10))

or something similar. This would work even with multiple predecessors, and if you text wrap the cell it will put them on separate lines

Karen_Pytel

This works beautifully, but it's backwards. The information from the task row is pulling up into the predecessor row. I would need it to work the other way.

Thank you for all of your help with this. I appreciate you troubleshooting!

L_123

then you just need to index.

=index(links:links,predecessors@row)

Matt Haas

@L_123 The Index method is almost there! This works if there is one, and only one, number in the predecessors cell. If there are two numbers in the predecessors cell, ex: "16, 15" the code doesn't work. Is there an easy way to account for this?