Convert Excel Cell Addresses into Hyperlinks with Dynamic Array Formulas
Question details
The user needs to convert a dynamic, spilled range of cell addresses into clickable internal hyperlinks.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing dynamic data arrays where a list of cell references generated by a formula needs to be transformed into active links for easy navigation.
- Observed behavior
- The goal is to flatten a dynamic array of text addresses and output them as functional internal hyperlinks pointing to specific locations within the same workbook.
Ensure your spreadsheet software is up to date and supports modern dynamic array functions such as TOCOL and LET, and verify that the source text strings are valid cell references.
Use the LET, TOCOL, and HYPERLINK Formula Combination
Apply a nested dynamic array formula to flatten the range into a single column and prepend the '#' symbol required for internal workbook navigation.
The HYPERLINK function requires a '#' symbol before the address text to recognize it as an internal location within the same workbook.
By leveraging the TOCOL function, you can transform a 2D spilled array (like A1#) into a clean 1D column. The LET function simplifies the formula by defining this flattened array as a variable, making the formula easier to read and compute.
Click on the blank cell where you want the new column of hyperlinks to begin.
Type the formula =LET(addresses, TOCOL(A1#), HYPERLINK("#"&addresses)). Replace A1# with the actual reference to your spilled array.
Press Enter. The results will dynamically spill down the column. Click on any of the generated links to verify it jumps to the correct cell address.

Handle Dynamic Array Formulas Seamlessly in WPS Spreadsheet
WPS Office provides robust native support for modern dynamic array formulas, including LET, TOCOL, and HYPERLINK. You can effortlessly manage complex data workflows and array calculations with high efficiency.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your cell address text data.
- 2. Input the dynamic array formula: Select the target cell and enter =LET(addresses,TOCOL(A1#),HYPERLINK("#"&addresses)).
- 3. Execute the formula: Press Enter to let the array spill automatically, converting the text into clickable links.
- 4. Navigate the spreadsheet: Click the newly generated hyperlinks to instantly jump to the referenced cells across your workbook.

Frequently Asked Questions
Why do I get a 'Reference isn't valid' error when clicking the hyperlink?
This error typically occurs if the '#' symbol is missing before the cell address in your HYPERLINK formula. It can also happen if the text refers to a worksheet that has been renamed or deleted.
Can I display a friendly, readable name instead of the raw cell address?
Yes. You can add a second argument to the HYPERLINK function to display a custom name. For example, use =LET(addresses,TOCOL(A1#),HYPERLINK("#"&addresses, "Jump to Cell")) to make all links display as 'Jump to Cell'.
Will TOCOL work properly if my spilled range contains empty cells?
By default, TOCOL includes blank cells and returns a 0 for them. To ignore empty cells in your dynamic array, you can add the ignore parameter to the function like this: TOCOL(A1#, 1).




