Function Problems

How to Make Excel Hyperlinks Update When a Worksheet Is Renamed

WPS Editor Oct 12, 2026 870 views

Question details

The user needs to configure Excel hyperlinks so that they dynamically adjust and continue to function correctly after a worksheet is copied and renamed.

How to Make Excel Hyperlinks Update When a Worksheet Is Renamed

Product: Excel
Device & OS: not provided
Scenario: Copying and renaming a worksheet that contains internal hyperlinks pointing to specific cells within that same sheet.
Observed behavior: Standard static hyperlinks continue to reference the original worksheet's name, causing the links in the newly copied sheet to navigate back to the old sheet rather than pointing to the corresponding cells in the new sheet.

Before you start

Ensure your workbook has been saved to your computer at least once, as the functions used to extract the current worksheet name require a saved file path to work properly.

Solution 1Recommended

Use a Dynamic Formula with CELL and HYPERLINK Functions

This is the most reliable method. By combining the CELL, FIND, MID, and HYPERLINK functions, you can force Excel to detect the current sheet's name dynamically and build a relative internal link.

This solution works by extracting the active worksheet's name from the file path and injecting it into the HYPERLINK function. It ensures that whenever the sheet is renamed or copied, the formula recalculates and updates the hyperlink destination immediately.

Extract the current sheet name

Select an empty cell (for example, B1) and enter the formula: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). This will display the exact name of the current worksheet.

Create the dynamic hyperlink

Select the cell where you want the hyperlink to appear. Enter the formula: =HYPERLINK("#'"&B1&"'!A1","Link Text"). Replace A1 with your target cell.

Test the hyperlink

Press Enter to save the formula. Click the link to ensure it navigates to the target cell. Copy and rename the worksheet to verify that both the extracted name in B1 and the hyperlink update automatically.

Use a Dynamic Formula with CELL and HYPERLINK Functions

Handling Sheet Names with Spaces: The single quotes enclosed in the formula ("#'") ensure that the hyperlink will not break even if you rename your worksheet using spaces or special characters.

Solution 2

Use Dedicated Reference Cells for Manual Updating

If you prefer a simpler approach without using complex text extraction formulas, you can manually maintain a reference cell containing the current sheet name.

Advanced Spreadsheet Capabilities

Create Dynamic Hyperlinks Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides flawless compatibility with advanced Excel formulas. You can easily utilize the HYPERLINK, CELL, and MID functions to build intelligent, dynamic worksheets that automatically adapt when duplicated or renamed.

1. Open your workbook: Launch WPS Spreadsheet and open the document where you need to create the updating hyperlinks.
2. Input the dynamic formula: Select your target cell and input: =HYPERLINK("#'"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"'!A1", "Dynamic Link")
3. Save and verify: Save your file locally. Right-click the sheet tab, select 'Rename', change the name, and test the link to see it update perfectly.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsReal-time formula calculation and intelligent error checkingLightweight, fast, and free to use for daily spreadsheet tasksFamiliar user interface ensuring a zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does my CELL("filename") formula return a #VALUE! error?

This error occurs because the workbook has not been saved yet. The CELL function relies on reading the file's directory path from your hard drive. Simply save your workbook locally, and the formula will instantly calculate correctly.

Can I link to a named range instead of a specific cell coordinate like A1?

Yes. If you have defined a scoped named range for your worksheet, you can simplify the HYPERLINK formula by directly referencing the defined name, such as =HYPERLINK("#MyNamedRange", "Go to Range"), bypassing the need to extract the sheet name dynamically.

Does this method work when linking to different workbooks?

The specific dynamic formula provided (=HYPERLINK("#'"&B1&"'!A1")) is designed for internal worksheet links only. Linking to external workbooks requires a different syntax that includes the full file path and workbook name enclosed in square brackets.

Excel & Data Problems