Forum Discussion
Help with Text Box Hyperlink to a cell based on formula.
If you want to create hyperlinks in text boxes that dynamically change based on the position of specific values in a column without using VBA, you can utilize the HYPERLINK function combined with the MATCH and INDEX functions.
Here is a step-by-step guide:
Assuming your data starts from cell K5, and "zzzzzzzz" is in column K, starting from row 5, and you have buttons in cells A1, B1, and C1:
Top Button (A1): For the top button, link it directly to cell K5:
In cell A1, write the formula:
=HYPERLINK("#Sheet1!K5", "Top")
Replace "Sheet1" with the actual sheet name.
Button 2 (B1): For the second button, link it to the cell below the "zzzzzzzz" value:
In cell B1, write the formula:
=HYPERLINK("#Sheet1!K" & MATCH("zzzzzzzz", Sheet1!K:K, 0) + 1, "Below zzzzzzzz")
Replace "Sheet1" with the actual sheet name.
Button 3 (C1): For the third button, link it to the first empty cell in column K below the last entry:
In cell C1, write the formula:
=HYPERLINK("#Sheet1!K" & MAX(ROW(Sheet1!K:K)*(Sheet1!K:K<>"")) + 1, "First Empty Cell")
Replace "Sheet1" with the actual sheet name.
These formulas use the HYPERLINK function to create links to specific cells in your sheet based on dynamic calculations using the MATCH and INDEX functions. They will adapt as your data changes.
Make sure to adjust the sheet name in the formulas accordingly. When someone clicks the hyperlink, it will take them to the specified cell in column K.The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Thank you for your assistance. That result is what I had tried. and it works if I click on the resulting Hyperlink in the cells A1, B1 and C1. But the problem I'm having is then having that assigned to a Text Box which is floating over cell data. Maybe I'm just failing the last step there?
I've tried setting the hyperlink of the textbox to Cell B1 or C1 in your example but rather than taking me to the cell linked to in B1 or C1, it actually just moves to B1 or C1.
This is a screenshot of an example I made showing the "Buttons" which are text boxes. Cells are different but you can see the formula in F2 is the "Mid" point. F3 is the "End" point.
If I made those cells hyperlinks they work. It's the Text Boxes that are the problem.
I've also tried selecting the text box and then entering a formula in the formula bar in case I was simply missing what most would consider an obvious step, and that may still be the case! At the end of the whole process, to make the Link in the text box I right click it, select "Link" and a dialogue box comes up with options allowing me to select a place in the document...
In the below image, you can see I have two Defined Names addr_Mid and addr_End which have the formulas I'm chasing, but they don't appear in the above image where I can select them as targets of the link. The "Value" shows as {...} but when I copy that formula to a cell, it does work. - See first image, cells F2 and F3
Hopefully those images help explain more the problem. If I'm allowed to upload or link the example file I can find a way to do it.
If you're dealing with text boxes and encountering difficulties linking them to dynamic ranges with formulas, here's a potential workaround:
Create Helper Cells: In a less visible area of your worksheet, create some helper cells where you can store the hyperlink formulas. For example, you can use cells D1, D2, and D3.
Link Text Boxes to Helper Cells: Instead of trying to directly link the text boxes to dynamic formulas, link them to these helper cells where you have the working formulas.
For example, if your hyperlink formulas are in D1, D2, and D3, you can link your text boxes to these cells.
Select the Text Box: Right-click on the text box you want to link.
Add a Link: Choose "Link" or "Hyperlink" from the context menu.
Select a Place in This Document: In the dialog box that appears, select "Place in This Document."
Select the Corresponding Helper Cell: Now, you should see the sheet and the cells, including your helper cells. Choose the appropriate helper cell (D1, D2, or D3).
This approach involves an extra step of linking your text boxes to helper cells that store the working hyperlink formulas. By doing this, you avoid the issue of text boxes not directly accepting dynamic formulas for hyperlinks.
Remember, in Excel, dynamic or formula-based hyperlinks may sometimes behave differently when used directly in certain contexts. Linking to a helper cell helps in such situations.
Give this a try, and hopefully, it helps you achieve the desired result in linking your text boxes to dynamic ranges.
Sorry, I still feel I'm missing something. While I can get the formulas for the helper cells working, and the resulting link IN the helper cell works. It's the Textbox that fails. In the following image you can see the formula working and indeed if I click on the "MID" hyperlink it takes me to the correct cell.
But then I right click the MID text box, select link, choose "Place in this document" and type in G2 (or $G$2 - I tried both). This makes the text box into a hyperlink and clicking on it take me to and selects Cell G2. Same was tried with END and G3 with same result.
https://cdmaustgroup-my.sharepoint.com/:x:/g/personal/bradw_cdmaust_com_au/EWpF2ueMV1RHkSS5NDLcVeEBj8pQSakrZuW6tUjkZQQ7nQ?e=ZFwhLD
- My knowledge of the topic is limited, I'm currently using Excel 2016 and unfortunately I can't quite work on/try out the formulas, so I'm at my wits' end.
Maybe someone else who has more knowledge about tables with pivots can help.
Thank you for your patience and understanding