How to build analytics for dashboard on a data sheet?
I had a couple of analytics columns on a dashboard that were locked and hidden, where I had a bunch of COUNTIF(S)s and SUMs (as a workaround without pivot tables) to create some charts for my dashboard. Turns out that when people change the sort, these analytics columns got messed up, and now my data is all over the place in those analytics columns, and the SUM functions are broken as a result of that.
Can the analytics be in a separate grid, and reference info from the underlying dataset on a separate page? How do I avoid this in the future??
EDIT - for example, if I were doing this in Excel, I'd do my analytics on a separate tab in the worksheet and reference the main data set.