Dec 17 2020 06:24 AM
Hello, trying to create a static link within a spreadsheet. Have titles on top of the document with links to the corresponding row/section. However, when I insert rows, the link continues to go to the initial cell and does not dynamically change. Does anyone know how to anchor the link?
Thank you!
Dec 17 2020 07:22 AM - edited Dec 17 2020 07:24 AM
What you have written is confusing. You ask first about wanting to create a static (presumably meaning "unchanging")link, but then describe the problem as"when I insert rows, the link continues to go to the initial cell and does not dynamically change."
So help here first to clarify.
If it's possible, also, attaching the actual spreadsheet, so long as it contains no confidential or proprietary info, is often very helpful. Words only go so far; images sometimes help; the actual spreadsheet is far and away the best way to actually see what you're describing , what you're working with.
Dec 17 2020 08:52 AM
@mathetes Thank you for the response. Attempting to clarify. Hope this helps!
Top of Spreadsheet (using individual text box for the following categories):
Issue:
- If I insert a row below, say Row 22, 24, etc., the hyperlink for "Zone 2" at top of spreadsheet no longer takes me to the "Zone 2" section below.
- I'd like the hyperlink at top to anchor on the corresponding row below (i.e., dynamically sticks with that row regardless of row insertions).
Row 20 - Zone 1 (merged row)
- Row 21 - akjasdlkfjs
- Row 22 - kjkjalkjdfas
Row 40 - Zone 2 (merged row)
- Row 41 - oljdsfkaj
- Row 42 - ljkalsdjflasj
Row 60 - Zone 3 (merged row)
- Row 61 - lkjsadlkfjalkj
- Row 62 - kikekna;kjlkj
Dec 17 2020 11:40 AM
I apologize, but I need to ask again if it's possible to attach a copy of the actual workbook. Or just the a sheet containing the dummy data such as you've provided. In particular I'd like to see how you implement that use of a set of text boxes at the top of the sheet, text boxes that contain the hyperlinks.
I'd also ask again for a more complete description not just of the task you're seeking to complete, but a description of the context.....it sounds to me as if there may be a deeper design issue, and therefore there may be a way to accomplish the bigger objectives (what the whole spreadsheet is about in the first place) using a different design.
I've not used hyperlinks within a spreadsheet before, other than to link to websites on "the outside"; let alone a hyperlink within a text box within a spreadsheet. It may be entirely legitimate, but if we can get at the bigger picture side of things, there may be a more straightforward method.
Dec 17 2020 12:04 PM
Ok see template attached. You can click on say "Money Movement" at the top and it will take you to the corresponding row/section. However, if you insert a row above Money Movement, it stays with the original cell and does not adjust. Hope this helps. Thank you.
Dec 17 2020 12:25 PM
@Dave_A30092 I don't know why those links aren't dynamic but I just tested (and found it works) using Named Ranges. So click on the heading where you want to link to and in upper left corner type a
NAME for that location. Here is an example where I named the Sales & Marketing row as "Sales":
Then in the edit link box choose that named location: