Updating sheet data before copying row

Werner Gerstacker

I have an intermediate sheet with only one row that pulls the results of some calculations from my master data sheet in order create an archive entry.

This intermediate sheet has an automation set up to copy the row into my archive sheet at a certain day and time every week, i.e. it extends the archive with a snapshot every time.

But it seems like the intermediate sheet does not get refreshed with the latest values before the row is copied into the archive because it only updates when I open, not when I run the automation.

Is there anything I can do about this?

It defeats the purpose of a timed automation if I have to open the intermediate sheet anyway just before the automation is run so that I can ensure I actually have a snapshot of the latest data in my archive, not the status of when the intermediate sheet was last opened a day ago.

The cell history shows the value that was copied from the intermediate sheet to the archive at the very bottom, but the latest value in the master sheet had actually changed more than a dozen times since then.

Werner Gerstacker

Hi @Paul Newcome and everybody else who is interested.

Smartsheet Support suggested the following approach:

Locking and unlocking the sheet will trigger an update of the formula in my interim spreadsheet.

I tried this and it seems to work fine - with one exception:

I needed to move the lock & unlock actions into two separate automations (with the lock one running first) because Smartsheet doesn't seem to adhere to the sequences as mapped above; it will basically do all three actions at the same time

This might result in the alert being sent before the lock/unlock, i.e. the alert is sent (or in my real case: the row is copied) before the action that triggers the update is carried out, and/or the 'Lock rows' action is carried out last, leaving my sheet in a locked state at the end of the automation.

Due to the granularity of the trigger, i.e it can only be run at the full hour, splitting the lock/unlock actions will result in my sheet being locked for an hour, but that's still better than having yesterday's or last week's values in the archive.

Paul Newcome

Do you have the TODAY function incorporated anywhere?

Werner Gerstacker

I do, indeed - it's the straightforward "=TODAY()" formula.

And this is the automation notification/alert I received today - it actually shows yesterday's date together with the value that's almost exactly 24 hours behind:

Paul Newcome

The TODAY function will only update when the sheet is activated. Different ways to activate a sheet include opening it, form entry, update request, cell link updating.

Werner Gerstacker

It's not just the cell that has the TODAY() function in it, the other cells are not updating either when I copy the row - and they are the ones linked to/from my master sheet that holds the information I need to archive.

Paul Newcome

Are you able to show a screenshot of the sheet?

Werner Gerstacker

Here you go - had to do a little bit of formula-trickery to convince Smartsheet the show the value with the right prefix:

The 'Last Change Date' cell is the one with the TODAY() formula.

Paul Newcome

OK. And what about the data in the {Direct-2-Plan} range? How/when is that updated? And can you provide a screenshot of your automation?

Werner Gerstacker

Actually, I was wrong - this is what the data flow actually looks like, the 'key values' in the sheet that doesn't archive the latest values comes from an overview sheet that holds multiple SUMIFS() in order to aggregate the info in the master table:

Still, I would expect either sheet to update proerly before I copy my row to the archive.

And this is the automation - pretty straightforward, I think:

Paul Newcome

So starting at the Master table and moving through the workflow... How does data get updated?

Werner Gerstacker

The owner of the 'Master Sheet' as well as the owners of the individual line items are updating the entries on an as-needed basis - either in the grid view directly or by using the edit view for a line item.

In order to check the overall status regularly, either of them might open the 'Overview' sheet now & then.

Paul Newcome

How does that data get from the Master to the Overview and from the Overview to the Key Values?

Werner Gerstacker

Sorry, @Paul Newcome - I must have missed the notification for your last question.

The flow is through a series of cell links to external references:

This is an example for a formula in the Overview sheet:

=-SUMIFS({2020 Summary}, {Region}, $Region2, {Stage Gate}, "1-Identify", {Range 2}, "Direct")

And this is a formula in the Key Values Sheet to show the value I need to archive:

=-1 * SUM({Direct-1-Identify})

Nothing special, me thinks, just a series of formulas that don't get recalculated before the snapshot for the archive is taken, i.e. the row is copied.

Paul Newcome

I had to re-read through the thread to remember exactly what we have and haven't tried and I noticed that in your screenshot of your automation the title specifies @2pm whereas you have the automation set to run at 11am.

Werner Gerstacker

That probably just has to do with the timezone, e.g. I had set it up run it at 11am PT (my time) while the person I ran it for is in ET, i.e. at 2pm.

It currently actually runs at 9am PT instead of 11am PT since the new sheet owner (after I transferred it to them) is in CT 😉

I don't think that matters much, tough, because when the workflow ran just an hour ago, the confirmation email showed this, which is not true for the data in the Master or Overview sheet:

Paul Newcome

You may want to contact Support for this one. It seems to me that there is a bug somewhere. There is no reason the copy row automation would pull correct data from one column but old data from another.