Is it possible to set up a data shuttle from a sheet summary?

Meredith Rhodes

Hello!

I have sheet summary formulas that collect various metrics on my data. The sheet / sheet summary is updated every time we attach a file to it (which is at least once every business day).

I would like to capture the sheet summary metrics at a specific time interval - maybe once per week - and add them to a new row on another sheet so I can keep track of the changing numbers through time.

I can see how this could work via a data shuttle from the sheet summary. But this doesn't look like a current functionality.

What other options do I have to make this happen?

Thank you!
Meredith

Kerry St. Thomas

I don't see any way to use Shuttle with a Sheet Summary. Here's a workaround though - this won't need you to trigger Shuttle; you can do it with core suite. It's not necessary elegant, but it can get the job done:

—CREATE a separate sheet. Have that sheet consist of one row, and make it essentially a KPI sheet that is a cross-sheet reference to your underlying sheet, that effectively does the same calculations as the Summary. This sheet SHOULD NOT include a column of type "Created Date".

—CREATE your archive sheet. Have all columns on your KPI sheet above, and ALSO include a column of type "Created Date".

—CREATE a COPY ROW automation on your KPI sheet. Change the trigger to "When a Date is Reached" and choose "Custom" to create your chosen cadence (weekly?) to COPY the row to your archive sheet.

Meredith Rhodes

@Kerry St. Thomas - I wanted to follow up with you on this. Using your advice, I have been able to record daily metrics by copying a single row without a created date over to an archive sheet with a created date.

On the sheet with the single row of data that doesn't have a created date on it - is there a formula that I can use in the sheet summary to record the date upon which anything on that sheet last changed?

Full disclosure - I broke AI asking it this question ;) [to record the date that anything on this sheet changes]

I may be able to do this on another sheet in my workflow - but I thought I'd see if you had any ideas.

Kerry St. Thomas

My first thought is make sure you have a column of type "Modified Date" in your original sheet (the one that's got all your metrics on it, not the one that has a single row). Then add a column of type "Date" that's the =MAX({Modified Date Range})

The limitation with this is the ONLY column type that will do a reliable TIMEstamp is "Modified" - this formula will only return the DATE.

Meredith Rhodes

Thanks, Kerry…

The sheet that is the source of data is modified in 2 ways, 1) by manually attaching a file, and 2) by our subsequent modification by adding notes or updating fields that aren't automatically updated upon attachment.

We really want to be able to isolate the date the file was last attached. I can see this in the attachments window, how to I report on this date?

Ideas?

Meredith Rhodes

UPDATE: I figured this out by setting up an automation on my primary sheet to record a date upon attachment. I did not know that was an option.

Thank you @Kerry St. Thomas and @Matt Lynn-PCG for your responses.