Populating Parent with Child Cell Info

gporter31876

I am new to Smartsheet and was hoping someone can help with this logic expression, or let me know if this is even feasible. 

We inspect equipment (blue light emergency towers) bi-weekly. I would like to create a sheet that shows these inspections and automatically creates a work order/assignment for our various repair technicians for any noted deficiencies.

I have created a sheet with the parent row serving as the individual inspection site, and the child rows are each of the weekly inspections. I have setup a rule to send out an update request if the inspection is within 3 days due, and our team is able to conduct their inspection. (That part makes sense and is an awesome feature - thank you Smartsheet!). 

In order to better visualize my data and understand the status of our blue light towers, I would like to automatically populate the Parent Row inspection question cells with the data from the most recent inspection. 

Is there a way to have these cells automatically populated with the last inspection data (e.g., if child cell is blank, populate parent with data from last child row with text)? 

 

Thanks!

Shaine Greenwood

Hello,

You might be able to use a formula to automate this. It would be based on a specific child cell being blank and a Date Modified system column.

1. Create a date modified system column with the steps in the help center: https://help.smartsheet.com/articles/1964567-system-columns
2. Add a formula to your parent rows that combines the INDEX and MATCH functions, and uses the MAX function to find the most recently updated child row. I have an example formula here: 
   
   
   
   =INDEX(Type2:Finish16, MATCH(MAX(CHILDREN(Modified1)), Type2:Finish16, 0), 1)
   
   
   
   The above formula will find the most recently updated child row, then looks over a table (all of the child rows) and populates the primary column in the parent with that primary column data from the child. (Screenshot attached for the function in action.)
   
   
   
   You'll want to modify the formula to reference the cells in your sheet.

R.Silber

Shaine: This is incredibly helpful and really well explained -- thanks so much! One question -- Let's say, in your above example, that I I just want the parent cell to the column "cost rate" to update to the most recently changed child cell beneath it. What happens if  I've updated the cost rate in child row 12 on one day; but the following day I update a DIFFERENT column like finish or product rate in child row 11; now the modified date for child row 11 will be the most recent? Do I just need to limit the span (say, where you have "Type2:Finish16" to "CostRate2:CostRate16") so that it will only recognize when those cells were most recently updated?

Thanks!