Cross sheet refrence formulas on a metric sheet keep showing errors until the sheet is opened
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.
Answers
-
This situation has come up before. There is a thread with a solution you can find here:
The solution was:
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
Hi Michelle! Thank you for sharing these articles. I've tried the record a date automation to refresh the sheets but it doesn't seem to work most of the time. I think the issue is that the cross sheet references take too long to refresh. The quick record a date function doesn't fix some of the heavier sheets (I'd say it works ~1/3rd of the time).
I'm going to attempt a Zapier/API solution that will open the sheets, wait 30 seconds, and then save the sheets.
-
I think that is the perfect way to go if your sheets are that large! Good luck!
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.1K Get Help
- 448 Global Discussions
- 154 Industry Talk
- 504 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 80 Community Job Board
- 512 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!