How to Create Excel Hyperlinks to a Specific Worksheet and First Blank Cell
Question details
Create formula-based hyperlinks to jump to a dynamically selected worksheet and land on its first blank row.
- Product
- Excel for the web (Office 365 Online)
- Device & OS
- not provided
- Scenario
- Managing a workbook with many sheets where users must select a target sheet from a directory and instantly navigate to it to input data.
- Observed behavior
- Users currently scroll through multiple tabs manually because macros are restricted. The goal is to automate navigation to the correct tab and the next empty row using only formulas.
Ensure the target worksheets exist and have continuous data in column A, as the formula relies on counting non-empty cells to locate the first blank row accurately.
Use a Nested HYPERLINK and ADDRESS Formula
Combines HYPERLINK, ADDRESS, COUNTA, and INDIRECT to dynamically jump to the next blank cell of a specified sheet. It works perfectly in Excel online without requiring VBA macros.
This method uses cell A2 as a reference point for your target worksheet name. By nesting functions, Excel counts how many rows have data, adds one to find the first empty row, and generates a clickable link to that exact location.
Type the exact name of your target worksheet in a cell, for example, A2. For an even better experience, use Data Validation to create a drop-down list of all your worksheet names in cell A2.
Click on the cell where you want the clickable hyperlink to appear.
Type the following formula: =HYPERLINK("#"&ADDRESS(COUNTA(INDIRECT("'"&A2&"'!A:A"))+1,1,,,A2),"Go to next blank cell")
Press Enter to submit the formula. Click the newly created 'Go to next blank cell' link to jump directly to the first empty cell in column A of the target sheet.
Create a Basic Hyperlink to a Specific Worksheet
If jumping to the first blank cell is not strictly required, use a simpler formula to just navigate to the top of the selected worksheet.
Create Dynamic Hyperlinks Easily in WPS Spreadsheet
You can implement the exact same formula-based dynamic hyperlinks in WPS Office Spreadsheet. It fully supports advanced nested functions, data validation dropdown lists, and seamless navigation between dozens of worksheets without relying on macros.
- 1. Open your workbook: Launch WPS Spreadsheet and open your multi-sheet workbook.
- 2. Create a drop-down list: Go to the Data tab and use Data Validation to create a drop-down list of your 50+ sheet names in cell A2.
- 3. Apply the hyperlink formula: Paste the combined =HYPERLINK(...) nested formula pointing to your drop-down cell in A2.
- 4. Navigate instantly: Click the link to instantly jump to the target sheet's first blank row, streamlining your workflow.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
This usually happens if the worksheet name referenced in cell A2 doesn't exist, is misspelled, or contains trailing spaces. Ensure the name in your reference cell matches the actual tab name exactly.
Can I link to a different column's first blank cell instead of Column A?
Yes. Change the "A:A" part of the INDIRECT formula to your desired column (e.g., "B:B"), and change the column index parameter in the ADDRESS function from 1 to 2 (for column B).
Does this formula work if there are blank cells scattered within my data column?
No, the COUNTA function calculates the total number of non-empty cells. If there are empty cells in the middle of your data, the count will be inaccurate, and you may land on an unexpected row. Ensure the reference column has continuous data.
How do I create the drop-down list for worksheet names?
Click on cell A2, go to Data > Data Validation. Under 'Allow', select 'List'. In the 'Source' box, type your sheet names separated by commas, or select a range of cells where you have listed your worksheet names.
