Cross sheet refrence formulas on a metric sheet keep showing errors until the sheet is opened

Christian SCS

I have 5 large sheets (10,000 rows, 45 columns) mostly populated by DataTable that I'm collecting metrics from on separate metric sheets (1 DT sheet → 1 metric sheet). The metric sheets collect the metrics via cross-sheet references. Some of the formulas "break" (breaking the dashboard visuals) but quickly fix themselves when the metric sheet is opened (I'm prompted to save the sheet once the error messages go away).

Note: The metric sheets (450 rows, 14 columns) are cell-linked to a "Combined dashboard metric" sheet

The formulas are mostly CountIF(S) and the most common error message is "incorrect argument set"

Example formula

=COUNTIFS({DT Sheet 1 | Checkbox}, "true", {DT Sheet 1 | Date}, <>"")

Does anyone know why this would be happening and/or how to stop the formulas from breaking without opening each sheet? I've attempted to use automations (Record a date) to force the sheet to save but that hasn't helped.

Michelle Choate 2

This situation has come up before. There is a thread with a solution you can find here:

https://community.smartsheet.com/discussion/78008/report-not-updating-formula-unless-the-underlying-sheet-is-opened-and-refreshed

The solution was:

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar