I have a dashboard that keeps on updating, how do I see last weeks data (In order to compare)?
Is there a way to view metrics every week, to auto-update from a week before?
Ah. Ok. So in the new sheet you will want to be sure to have a system generated column that captures the created date for when the new rows are copied over. In the top row of this sheet, you would use this formula to pull the most recent row's data to the top:
=INDEX([Column Name]:[Column Name], MATCH(MAX([Created Date]:[Created Date]), [Created Date]:[Created Date], 0))
Put this in the first row of every column that you want to display the data for, and then reference this top row in your dashboard.
CERTIFIED SMARTSHEET PLATINUM PARTNER
Efficiency Live: The Ultimate PM Toolkit Workshop
My suggestion would be to find a way to capture the data from the sheets themselves using a Copy Row function. Unfortunately you cannot automatically capture an entire dashboard.
I need a formula, to capture data from a week before, in some way, so that I can add it to metrics.
What formula are you currently using to capture your metrics from the current week?
Below is my current formula, do I need to create a new column weekly?
or is there a formula to do that?
You could set up a separate sheet and use a Copy Row automation at the end of every week or before your dashboard updates. This would capture that static data each week.
Oh, now I get it,
this would be pretty good, but would still require some manual work, like selecting the new cell for the new metric.
Any solution for that?
I'm not sure I follow. You shouldn't have to add anything once you create the new sheet along with the automation.
It will copy the data over to the new sheet. Not move it. The formula on the source sheet will still continue to run as is.
I undertand that, let me explain,
The new sheet, will have a new row every week,
in order to view metrics on a dashboard, the cells you want to view, need to be selected,
if you select cells for this week, next week will be a new row, that will not be in the window you've preiviusely selected, unless if you select more then 1 cell, to be in the same window.
Are you trying to SHOW the previous week's metrics on the same dashboard, or just capture them for historical documentation?
yes, I want to show on the same dashboard, the numbers from last week, in addition to the current numbers.
that is absolutely amazing!
@Paul Newcome ,
Would you know why it's the link is not showing any data?
not showing data, and not showing error.
Do you have any rows that are blank in the column you are pulling from but has a date in the created date column?
there are definetely blank rows, is that the reason?
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
I am trying to write a formula based on two sheets. One sheet, Team Members, that has the following columns: Column 1 - Region Column 2 - Industry Column 3 - Contact The other sheet has many columns, but it has matching columns of the same Column 3 - Region Column 4 - Industry Column 10 - Contact I've tried a bunch of…
Hello! I'm looking for some help with the Netdays formula. I am trying to create a helper column that tracks the number of days between a date column titled "Start Date" (when a project began) and a date column titled "Completion" (when a project is completed). My current formula, which is just a start, is this:…
Hi, I have a sheet with ranges identified as SUBJECTM and FLAGM. On my summary page, I have a list of all variables, with the column identified as Metric. In the main sheet, the subject field is a series of checkboxes with the ability to select multiple options (think : maths, english, french, art). I want to count the…
©2024. All Rights Reserved Smartsheet Inc.