Include Previous Update Request Response in Current Update Request

MariaCurtis

Hi!

This is my first question asked on the Smartsheet Community and I'm hoping someone can help me, because this would greatly help our users. My boss and I run the HR PMO for our company, and we use Smartsheet to collect project updates for all active projects in the HR portfolio. Updates are requested from what we call our "Report Out Managers" (sometimes the PM, sometimes functional lead) every other week.

We use automation to send out these update requests, and then we use another automation to clear out the responses before the next request is sent out. A screenshot of what the request looks like is shown for reference. Every other week, we ask for an update on the 3 fields shown in the red box.

However, often people can't remember what they put the last time, and if they are responsible for providing updates on more than one project, this can be quite a difficult thing to keep track of.

We were asked if we can update the notification to include the response they inserted in the previous update. I have tried to figure this out without creating another column and manually copying the updates before the cell is cleared. I am not seeing a way to do this in a non-manual fashion. Has anyone found a solution?

Paul Newcome

Have you tried just not clearing them out before sending the update request?

MariaCurtis

Hi Paul,

Yes, we have considered that. But if we keep the previous response in the cell, then it will look like they've provided an update in our reports each reporting period, when in fact they haven't. We have lots of reports created based on that cell being blank for the current reporting period.

Paul Newcome

Where would you want the previous entry listed then?

MariaCurtis

Ideally in a new column. I can't find an easy or automated way to do that.

If I use a formula, I can't see how the formula can keep the value there when the update entries get cleared.

I don't see a way to use a workflow to populate it and store it there until the next update.

I THINK I have a super complicated way I could automate this (to be tested), but that may not be any easier than just copying and pasting the values after each review into the new column myself.

Paul Newcome

You could set up a copy row automation to copy the row when the data changes. This will copy the row over to another sheet as static data. You can then use an INDEX formula with cross sheet references to pull in the most recent copy for that row/column (assuming you have a unique identifier on each row).

MariaCurtis

Yes, we do have a unique identifier!

This is along the same route I was thinking but your solution seems easier than the one I was thinking of. I started off the same route by copying the changed rows to a new sheet (and have set up that automation). How can I grab the most recent copy of that row though? That's the part I wasn't sure how to handle.

I was going to use dates and formulas to help me identify when rows should be removed from the destination sheet and then use an automation to remove those rows. But I would rather learn from you how do grab the most recent!

MariaCurtis

I tried the following formula, but it's only working for the first item on my sheet for some reason. Can you help me understand why?

=IFERROR(INDEX({Unique ID: Required Field}, MATCH(MAX(COLLECT({Date Row Added: Date Row Added}, {Unique ID: Unique ID}, =[Unique ID]@row)), {Date Row Added: Date Row Added}, 0), 19), "")

Paul Newcome

Try something more like this:

=IFERROR(INDEX(COLLECT({Column To Pull From}, {Unique ID}, @cell = [Unique ID]@row), COUNTIFS({Unique ID}, @cell = [Unique ID]@row), "")

The logic:

We COLLECT all rows with that ID together. Then we use the COUNTIFS to tell the INDEX function which row from that list to pull. So if the Unique ID is in the copy sheet 10 times, then the COUNTIFS will output 10 which tells the INDEX function to pull the 10th entry from the list generated by the COLLECT function.

MariaCurtis

Thanks Paul - I tried it but it's not working for me. It is returning blank when it should be returning a value.

I did end up getting things to work using my more complicated route, but when I have more time I would definitely like to keep working at this to get a simpler solution in place.

Paul Newcome

If you can provide some screenshots of the data, that would be helpful.