Cell Value Capture for Monthly Reporting

Garrett.Ricker

Hello,

I am trying to build an executive summary dashboard and would like to capture cell values at the beginning of every month to compile on a monthly basis. I have thought about using a row copy automation however i need to build a "capture sheet" for the dashboard to reference for several data points (total invoice balance, break down of those balances by party, balance of invoices over 60 days, etc.). Is there a way to capture and copy a cell value that is derived from a formula to another sheet on a monthly cadence being directed to different cells?

For Example:

When date is the 1st day of the month, log the cell value is green to the specified cell for that month.

Darren Mullen

@Garrett.Ricker I've built out solutions that have done this sort of thing. You are on the right track with a copy row automation. You'll have to create an "archive" sheet to collect all of the rows you copy monthly.

You'd need to use some formulas to search the data based on any dates you have in the archive sheet to display them on your dashboard.

I think what you are looking to create is a summary sheet that pulls in data from your archive sheet and displays the data in a specific cell on your summary sheet based on the data in the archive.

Conceptually this can be done, you would just have to ensure you are tracking the dates you need to track in your archive and then have cross sheet formulas that search the archive and pull in the appropriate data into your summary sheet.

It's a lot to develop in a community post, but I do have something similar to this in my book's example solution if you needed an example implementation that utilizes monthly row copies to archive data for reporting purposes.

https://smartsheetbook.com

Paul Newcome

It shouldn't be too much more complicated than what you already have. You should be able to use an INDEXCOLLECT along with a Created Date column to pull everything over.

If you have a Created Date column on the Archive sheet (the one receiving the monthly copies), you can leverage this in some INDEX/COLLECT formulas to pull the data from the Archive sheet in vertical format to your dashboard data sheet in the horizontal format.

Pulling for July of 2023 would look like this:

=IFERROR(INDEX(COLLECT({Copy Sheet Value Column}, {Copy Sheet Label Column}, @cell = [Table Name]@row, {Copy Sheet Created Date Column}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2023)), 1), "")