Formatting Links

Hello everyone!

I was wondering if there was a better way to format my links. I have a list of contacts with relevant info for each and have made "buttons" for each one linking to its position in the list. If I choose to link to the cell where the title is or the defined name, the link brings me to where the title is visible on the very bottom of the sheet. As a workaround I've selected a a cell that's further down so that my link brings the info to the top of the screen, but if I need to insert or add information in the future the links will no longer work since they're referencing an arbitrary cell as opposed to title itself.

Clicking the "STAPLES" button here:

1.PNG

Will link to here:

2.PNG

With the STAPLES title on the bottom if I link to that cell or the defined name as opposed to here:

3.PNG

Where I want it to go.

Any suggestions?

You can do this by changing your defined Names which you use to link to. Once you've made a Defined Name for each link, go to 'Name Manager' on the 'Formulas' tab and change the references to use Offset.
For example, change '=Sheet1!$B$22' to '=OFFSET(Sheet1!$B$22,4,1)' to make the link now go to C26.
Does that help?

I just did some more testing and it's a bit tricky. You need to make the link before you change the formula in Name Manager and you can't change the 'name' of the Defined Name or the link will fail.
So, it's something like this:
1. Make a defined name for each title cell (for example, 'DN_Staples' referring to $B$22).
2. Go to each cell where you want links and make the hyperlink to the 'Defined Name' you made for each.
3. Go to Name Manager. Select the Def.Name you want to change. Do not click 'Edit' but just use the 'Refers to' box at the bottom. Change the reference from (for example) =$B$22 to (for example) =OFFSET($B$22,2,1).
4. Repeat as needed.

If you change the 'name' of a Def.Name, the link won't work anymore and you can't remake it as the amended Def.Name will no longer appear in the list when you try to Insert/Edit Hyperlink. The only way round is to remove both the link and the old Def.Name then repeat the above process.

Because this is so long-winded, I don't know if it's really a good solution for you, sorry.