Cell Behaviour for Cross Sheet Reference Formula with Daily Source Sheet Data Refresh via Data Shuttle

Question

Hello,

I have a metrics reporting sheet that relies on cross sheet reference formula to count rows from a formula column in a source sheet (let's call it Sheet A) that refreshes with data via data shuttle (set to 'Replace all target sheet rows with the data from the input file') once per day at 8 AM UTC (I am in PST).

One formula I have in the metrics reporting sheet cell is as follows:

=IF(TODAY() >= DATE(2026, 3, 1), IF(COUNTIF({Sheet A}, "ON TIME") + COUNTIF({Sheet A}, "CLOSED LATE") + COUNTIF({Sheet A}, "LATE") = 0, "#N/A", IFERROR(COUNTIF({Sheet A}, "ON TIME") / (COUNTIF({Sheet A}, "CLOSED LATE") + COUNTIF({Sheet A}, "ON TIME") + COUNTIF({Sheet A}, "LATE")), "#N/A")), "")

I understand why the cell with that formula would flip from #N/A → 100% as the data refreshes (Sheet A empties) and it is recalculated (Sheet A is populated), but why would that particular step break & stop at #N/A like it did on April 8th, 2026 if the data shuttle ran without issues?

Screenshot of cell history for reference:

KarinR · Answer

@MichelleWatkins_GPP  - Very clever, I like that!

ETA: We use automation in the early morning hours to update the Today Date helper column.

ctran · Answer

I am opening the metric reporting sheet that contains the formula in my original post, not the sheet reference. The sheet reference doesn't use the Today function in any calculations.

I've opened a Customer Support case as well to try and troubleshoot further…they indicated it wasn't likely to be the Today function because of how the logic is set up in the formula. Nonetheless, I'll set up the automated helper column to ensure there's sheet activity to trigger the daily refresh.

For the time being, it seems like the activity log for Sheet A shows that Sheet A formula impacting the metric errored out, triggering that last cell link swap to #N/A… The data shuttle runs don't reflect any issues, so I'm thinking it's a possible parsing error that interrupted the usual calculation once the sheet populates again (that would flip #N/A → a valid metric). So far, I haven't been able to catch the issue when it happens, but the trouble is that it has been inconsistent. 😅

MichelleWatkins_GPP · Answer

Sharing one thing my consultants include in their solutions for our clients - adding a Sheet Wake Up automation based on a Sheet Wake Up column being checked/unchecked.

You will see in Matt's example below he also includes clear cell value and change cell value to trigger the automation that wakes up the sheet.