Automated Dashboard Metric Updates

Sophie Knudson

Hello!

I have a dashboard for one of my projects where I use a metric widget to share updates on each objective. I have an underlying sheet that sends out update requests every two weeks to the leaders of each objective. I have an automation within the sheet that archives old updates to an Archive sheet.

I want the latest update from the Updates sheet (top row) to be automatically populated into my Dashboard's metric widgets, but once they get archived, instead of having the next row (the new Row 1) show up in the Dashboard, the widget displays a "Missing Data" error.

Has anyone solved this issue before?

Paul Newcome

You could use formulas to pull in the most recent entry into Sheet Summary fields and then reference the Sheet Summary fields in your Metrics Widget.

Paul Newcome

You could use formulas to pull in the most recent entry into Sheet Summary fields and then reference the Sheet Summary fields in your Metrics Widget.

Sophie Knudson

Oh! I have not done this before! Thanks so much!

Paul Newcome

Happy to help. 👍️

My suggestion for a formula would be along the lines of...

=INDEX([Column Name]:[Column Name], 1)

Since you aren't' using a cell reference, you don't have to worry about it running into the same issue when the rows are moved. The INDEX function is basically saying to look a the entire column and pull from the first row.