VERA

Appearance

Formula Rules ​

Critical rules for writing Google Sheets formulas in the reporting spreadsheet. These are derived from confirmed failures and are non-negotiable.

The golden pattern ​

Every dashboard formula should follow this structure:

=IFERROR(
  LET(
    base, FILTER(
      HSTACK(sheet!col_A, sheet!col_B, sheet!col_C),   ← inline sheet refs
      (sheet!status_col="active")*(sheet!cat_col="x")   ← inline condition
    ),
    f_x, CHOOSECOLS(base, 1),    ← split filtered result into named cols
    f_y, CHOOSECOLS(base, 2),
    ext, 'Other Sheet'!A2:C,     ← pre-load cross-sheet ranges for LAMBDA
    computed, MAP(f_x, LAMBDA(v, SUMIF(ext, v, ...))),  ← MAP for element-wise ops
    SORT(HSTACK(f_x, f_y, computed), sort_col, FALSE)
  ),
  {"No data","",""}
)

Key principles:

  • FILTER condition inline — never in a LET variable
  • FILTER once, CHOOSECOLS to split
  • Pre-load cross-sheet ranges as LET variables before any LAMBDA
  • MAP/LAMBDA for computed columns
  • IFERROR wraps the entire LET

Rule 1: Never compare a LET range variable with = ​

Wrong:

LET(
  p_status, '02. Projects'!F2:F,
  filtered, FILTER(data, p_status = "active")  ← BROKEN
)

Why it breaks: A LET variable holding a sheet range evaluates as a scalar (first cell only) when compared with =. This silently collapses 999 rows to 1 row, causing "FILTER has mismatched range sizes" errors.

Right:

FILTER(data, '02. Projects'!F2:F = "active")  ← compare sheet ref directly

Rule 2: Never use ARRAYFORMULA inside LET ​

ARRAYFORMULA is an evaluation-mode modifier. It does not expand correctly within LET's variable scoping.

Wrong: ARRAYFORMULA(SUMIF(...)) inside a LET block

Right: Use MAP(array, LAMBDA(v, SUMIF(...))) instead.

Rule 3: Never put cross-sheet references inside LAMBDA ​

LAMBDA creates its own evaluation scope and cannot resolve 'Sheet Name'!range references.

Wrong:

MAP(ids, LAMBDA(id,
  SUMIF('01. Time Entries'!E2:E, id, '01. Time Entries'!G2:G)  ← BROKEN
))

Right:

LET(
  te_proj, '01. Time Entries'!E2:E,   ← pre-load BEFORE the LAMBDA
  te_hrs,  '01. Time Entries'!G2:G,
  result, MAP(ids, LAMBDA(id, SUMIF(te_proj, id, te_hrs)))  ← use LET vars
)

Rule 4: SUMIF and VLOOKUP return scalars inside LET ​

Without ARRAYFORMULA (and you can't use that — see Rule 2), SUMIF and VLOOKUP return a scalar. Wrap them in MAP to iterate:

totals, MAP(project_ids, LAMBDA(pid,
  SUMIF(te_project_col, pid, te_hours_col)
))

Rule 5: Computed arrays vs raw range references ​

  • Computed array (result of FILTER/CHOOSECOLS/MAP): broadcasts correctly in = comparisons. Using it in SUMPRODUCT conditions inside MAP works: SUMPRODUCT((f_pm=pid)*1).
  • Raw range reference ('Sheet'!A2:A): does NOT broadcast correctly. Never store in LET and compare.

Rule 6: IMPORTRANGE ghost rows ​

IMPORTRANGE on an empty source table often leaves a phantom row that passes <>"" checks. Guard against this:

=IF(SUM(te_hours)=0, {"No data","",""}, LET(...))

Or use ISNUMBER(col)*(col>0) as the filter condition.

Rule 7: QUERY string matching is case-sensitive ​

FILTER = is case-insensitive. QUERY WHERE is case-sensitive.

  • 22. Projects Master uses "Active" (capital A)
  • 02. Projects uses "active" (lowercase)
  • In QUERY: WHERE G = 'Active' — use capital A

Status threshold reference ​

Health status:
  pct_used >= 1.0   → Over budget (🚨)
  pct_used >= 0.80  → Warning (⚠️)
  pct_used <  0.80  → Healthy (✅)

Retainer burn rate:
  projected_pct > 1.50  → Critical overpace
  projected_pct > 1.10  → Warning overpace
  projected_pct >= 0.90 → On track
  projected_pct >= 0.50 → Warning underpace
  projected_pct <  0.50 → Critical underpace

Quoting rule for sheet names ​

Only tab names with special characters need single quotes in formula strings:

  • =IMPORT_DB("01. Time Entries!A:H") — no quotes needed
  • =IMPORT_DB("'22. Projects Master'!A:P") — quotes required (space in name)

When referencing hidden mirror sheets directly in formulas, the same rule applies:

  • '01. Time Entries'!A2:A — quotes required for all tab names with spaces or dots in cross-sheet references

Debugging checklist ​

If a formula returns unexpected results:

  1. FILTER mismatch — are you comparing a LET range variable? Move the condition inline.
  2. Single value returned — is a SUMIF/VLOOKUP not inside MAP? Wrap it.
  3. LAMBDA error — is a cross-sheet reference inside LAMBDA? Pre-load it as a LET variable.
  4. Ghost row from IMPORTRANGE — guard with IF(SUM(col)=0, fallback, LET(...)).
  5. QUERY returns nothing — check case sensitivity; use capital A for Active status.

VERA by talktalkmake