Using countifs across sheets to hold metrics for reports

bbemis

We have a sheet that lists all of our projects (there are over 300 at the moment). We used to keep all of our metrics at the top of the sheet, but every now and again, someone would sort by a value that caused the metrics to get mixed into the project list.  So when cross-sheet formulas (vlookup, countifs, etc) became available, I created a new sheet that totaled all of the various metrics regarding our projects separately, rather than keep them in the same sheet.  So, I have the original project sheet and the metrics sheet.  The metrics sheet is essentially a large set of countifs.

Now, I'm building out dashboards and reports. I show the metrics on the dashboard, and if someone wants more info, they can click on it and go to the report.  

My question is actually about the metrics sheet...if I don't go in and save it regularly, will the metrics get updated on my dashboards?  Or will they only show the last "saved" number?

Mike Wilday

Good question. Based on the fact that its a formula... you would probably have to access the sheet regularly to update the numbers. Could an employee? OR other experienced user confirm? 

J. Craig Williams

You Metrics/KPI will be updated without someone viewing it. I have seen no issues with this except a slight delay sometimes.

The only "if a trees a falls in the forest" issue I know of when the formula reference the TODAY() function in any way. Then you will need to someone refresh that sheet. That is true whether the formula is on the Metrics/KPI sheet or the sheets feeding it.

Craig

bbemis

Thanks, Craig.  Yes, I am using the TODAY() function...that would explain the strangeness I was seeing.  Guess I'll have to plan to save that Metrics sheet on a daily basis    

J. Craig Williams

If you already use Zapier, try this:

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

If not, for small numbers of sheets, it is the way I go. 

Craig