Run reports based off of certain cells without tying it to specific data

Matthew Wilde

Hey all,

So I am trying to pull historical data off of weekly back ups. Specifically I am trying to pull project health (color ball symbols) off the last 8 weeks and display them on a dashboard horizontally broken out by columns of each week. I tried cell linking them to the top 8 rows and it works until a new back up is made and it pushes that linked cell down and doesnt update the new cells. Is there a way to set up formulas to always capture data in a specified time frame? Possibly using the create date of the rule to capture data in the corresponding column and spit it out in another column?

Andrée Starå

Hi @Matthew Wilde

How are you doing the backups?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Matthew Wilde

Good morning @Andrée Starå , I threw together a quick dummy sheet. On the left is the captured time our back up pulls the data. On the right is our project health. Each week a new back up is ran and the entry is placed in row 1 pushing all other entries down. I am trying to find a way to capture the data but maintain the only the top 8 rows to display in a dashboard to show historical health of the project. Currently every way I set it up, the formulas stick to the row and move down when new entries are generated.

Andrée Starå

@Matthew Wilde

I'd recommend using a report instead and set it up so it only shows the top eight rows.

Make sense?

Would that work?

Matthew Wilde

@Andrée Starå When I run a report it stacks the info in a column. My struggle is trying to display it across a dashboard horizontally.

Dale Murphy

@Matthew Wilde , you want each column to represent a week? and roll those weeks (so left-most is (Current Week - 8)?)

So the "metric sheet" would have eight columns and two rows? (not including headers and labels)?

One quick note: trying to combine dates in a column with other info is tricky.

Matthew Wilde

Would only have 1 row. Just the stop lights. I want to have a report to display each week(labeled in the column name) and then just the stop light for the corresponding week. Once that is set up, I need it to update the report as new backups are ingested from the workflow.

Dale Murphy

My observation is that the configuration you described has maintenance associated with it. You need to be building columns with new names every week.

I would recommend putting the dates into cells, having them change by formula every day or week, and have your ingested data matched to those values.

I can lay that out if it makes sense.

Matthew Wilde

I appreciate the the suggestion but thats not going to work for us. We have 36 projects auto backing up and always adding more. Needed something for reporting to constantly spit out the new data each time a new back up is made. Manually updating over 300 cells a week would be really tedious.

Andrée Starå

@Matthew Wilde

I hope you're well and safe!

Excuse the late response.

Crazy times and I missed coming back to you.

Have you solved it, or do you still need help?

Happy New Year & Stay safe!