Forum Discussion
Help with Text Box Hyperlink to a cell based on formula.
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