Pulling data from predecessor row into current task row
![Karen_Pytel](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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!
Answers
-
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.
-
@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?
-
What do you mean by predecessor? the row immediately above the analyzed row?
-
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.
-
Do you have an autonumber column?
-
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
-
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!
-
then you just need to index.
=index(links:links,predecessors@row)
-
@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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!