Audit formula precedents and dependents

I need a full audit of this workbook’s formula precedents and dependents before I hand it to finance. Start on the Profit & Loss sheet: list every formula that pulls from other sheets, show the exact cell addresses it reads from, and indicate whether any referenced range is empty, contains text, or #N/A. Flag formulas that point to external files or to entire columns. Do this in a new sheet named Formula Audit and mark high-risk items in red.

Before I send the audit to the controller, make the results actionable. Put the totals that change due to broken links or missing ranges at the top, summarize how many unique precedents each critical summary cell has, and highlight any precedents that are volatile (INDIRECT, OFFSET) or point outside the file. Add a short note beside each flagged formula describing the minimal fix: repair link, replace volatile reference, or restrict range.

I refreshed data and noticed the Net Income cell didn’t change while several underlying department totals did. I’m afraid some formulas are still pointing to old sheets or an external workbook. I can’t tell which summary formulas are out of date without breaking anything. Which cells should I inspect first and what’s the fastest, lowest-risk way to prove where the break is?

Every month we spend hours chasing formulas that point at last year’s workbook or at deleted sheets. I want to stop firefighting. Which habitual checks should I add to our close checklist so we catch bad precedents early, reduce rework, and make reviewers trust the numbers? Keep it to a few repeatable steps the team can do in the first 30 minutes of close.