We have a straightforward opinion on this problem: spreadsheet software should not require users to become amateur programmers just to build a working index. The user here did everything right, they understood the HYPERLINK function, they knew how to reference cells across sheets, and they correctly identified that dragging a formula should increment the cell reference. Yet the hyperlink stubbornly pointed back to A2 on every row. That is not a user error. It is a design gap.
The root cause is subtle but instructive. When Excel sees `=HYPERLINK("#SheetName!A2",SheetName!A2)`, it treats the first argument, the link location, as a plain text string. Dragging the formula updates the second argument (the friendly name) because that is a standard cell reference. The first argument, wrapped in quotes, remains static. The fix requires either manually editing each hyperlink or nesting an INDIRECT function to build the reference dynamically. For someone managing dozens of sheets, that workaround is not a solution, it is a tax on their time.
What this tells us is that spreadsheet tools still treat hyperlinks as decorative shortcuts rather than as data that should move with your structure. The user wants their index to behave like a living table of contents: when they add rows to a source sheet, the hyperlinks should follow. That expectation is reasonable. Modern data tools should make cross-sheet navigation as fluid as scrolling through a single table. Instead, users are left hunting through forums and AI prompts, hoping to find the magic incantation that makes the software cooperate.
The practical takeaway is this: if you are building indexes or dashboards that pull from multiple sheets, plan for this limitation upfront. Use helper columns to construct the link string with concatenation, or switch to a table structure that supports structured references. But also recognize that the burden should not be on you to reverse-engineer workarounds. A tool that claims to empower your data journey should not make you feel like you are fighting the formula bar. Until that changes, save yourself the frustration by testing your hyperlink logic on a small sample before scaling across your workbook.