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:

Report not updating formula unless the underlying sheet is opened and "refreshed"?

James Keuning

Apr 5, 2021

I have a report that brings in a bunch of sheets with one of those "this task was due 15 days ago" or "this task is due in two days" types of formulas. So this report has a due date and this text narrative.

I run the report, and the text is wrong. Like Due Date will show a date in the past, and the narrative will say that the item is due in two days. So I open the underlying sheet, and close it, and the report updates.

Is there something I need to do to make these formulas update?

---

View Post

The solution was:

Daily Update Solution without the need to open the sheet, use Zapier, or similar

Andrée Starå

Jan 6, 2021

Hi everyone,

Happy New Year!

I hope you're well and safe!

I've developed a way to use the newly released Record a date feature to update sheets with today's date automatically without the need to open it or similar.

1. Add a so-called helper column. (Date). We can call it Today Helper and add a date before the current day.

2. You'd then use an Automation Workflow as described below.

Trigger: When a date is reached

Every day starting on 2021-01-06 (today or earlier)

02:00 (select the time that makes the most sense for the process)

Conditions

Where Today Helper is in the past

Record a date

Record a date in Today Helper

https://us.v-cdn.net/6031209/uploads/1R2CO9WEE5M5/screenshot-of-ghost-browser-282021-01-06-2c-14-22-21-29.jpg

This could also be structured so you'd use a so-called helper sheet for the process above and then link it to all other sheets where you'd need the daily update.

Let me know if you have any questions!

I hope that helps!

Stay safe, and Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as helpful. It will make it easier for others to find a solution.

---

View Post

Christian SCS

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.

Michelle Choate 2

I think that is the perfect way to go if your sheets are that large! Good luck!