Forum Discussion
Help with Text Box Hyperlink to a cell based on formula.
I have a shared workbook that multiple people access and am trying to improve it with "Buttons" to assist navigation. I have Text boxes but the location I need to link to varies. I've made an example file though I can't seem to find a way to add it just now.
Essentially, in Column K from cells 5 onwards I have my data. One of the entries has the value of "zzzzzzzz" but as more entries are added to the column and sorted, the location of this "zzzzzzzz" row moves.
Button 1 takes me to the top and simply goes to K5. I want the Button 2 text box hyperlink to take me to that cell, or really the cell below it. And Button 3 would then take me to the first "Empty" cell in column K5 and below.
I've got formulas in other cells for testing that do result in the correct cell selection. I've also tried Named Entries with no success.
I'm trying to avoid Macros and VBA as honestly I would consider my skill set as an "advanced dabbler" and my past experience is nothing but problems when trying to share a macro/vba enabled workbook among multiple staff entering data at same time.
Thanks in advance for any guidance you can provide.
Nb. Not sure if I can't add the example workbook because I'm a newly registered account etc, or if simply not allowed but I can provide it if someone requests either by email or link if that is allowed here.
5 Replies
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.
