Overview
Standard Excel hyperlinks break when you rename sheets or move cells. This workbook demonstrates how to build formula-driven hyperlinks that dynamically resolve their targets using HYPERLINK(), INDIRECT(), and CELL() functions. The links always point to the right place, even after structural changes.
Download the Excel file (.xlsx)
What's Inside
- Navigator Sheet: A dynamic table of contents with hyperlinks that auto-update when sheet names change.
- Simple Example: Side-by-side comparison of correct hyperlinks, offset hyperlinks, and "empty" hyperlinks to illustrate common pitfalls.
- Template Sheets: Time series and general templates showing how to integrate dynamic navigation into real financial models.
- SumProduct Standard Structure: Includes Cover, Style Guide, Model Parameters, Timing, and Error Checks sheets following SumProduct's best-practice model layout.
Key Techniques
- HYPERLINK + CELL: Uses
CELL("filename")to construct workbook-relative links that survive file moves. - Dynamic Sheet References: Sheet names are pulled from a list so the Navigator always reflects the current workbook structure.
- Hyperlink Pitfalls: Demonstrates what happens when hyperlinks go stale — offset links and empty targets — and how to prevent it.
When to Use This
Use dynamic hyperlinks in any workbook with more than 5 sheets, especially financial models where sheet names change during development. The Navigator pattern is a SumProduct standard — every model we build includes one.