SOLVED

Hyperlinks

Copper Contributor

OS: Windows 10

Excel: 365

 

I would like to add a hyperlink to a place in my document.  I do not want it to have a specific cell reference because I may add rows to the page where my hyperlink points to. 

For instance, I have assigned a hyperlink (place in this document) on sheet named "work orders" and it points to cell C7 on sheet named "dates".  This works if I never add rows to the destination sheet.  If I add 3 rows above row 7 on sheet "dates", then my hyperlink is off by 3 rows.  I do have unique headings to each area I want the hyperlink to be set to.  i.e. "Work Order 5", "Work Order 12". 

 

Can I assign a hyperlink to a unique name within the sheet and it will always point to "Work Order 5" no matter which row it is located?

5 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@tlwellborn 

(Same as Hyperlinks )

 

Select C7 on sheet Dates.

Click in the address box on the left hand side of the formula bar and replace the cell address with a name, for example WorkOrder5 (don't use spaces in the name) and press Enter.

Switch to the sheet with the Work Orders sheet and right-click the cell with the hyperlink.

Select Edit Hyperlink... from the context menu.

You should now see a section Defined Names in the 'Or select a place in this document' tree, with the name that you created under it.

Select it, then click OK.

it worked!!!  Thanks.

@Hans Vogelaar 

I have a similar situation, except when I click on the link, I would like the selected cell to appear mid-page, not at the bottom of the page. Is there anyway to autoscroll the page up about 15 rows?

@Canary5 

If you're willing to use VBA:

Right-click the sheet tab of the sheet with the hyperlink.

Select 'View Code' from the context menu.

Copy the code listed below into the module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    ActiveWindow.SmallScroll Down:=15
End Sub
What a fantastic idea! I tried it and unfortunately, I have too many hyperlinks going up and down the entire 5000 line spreadsheet. Now the reverse links behave funny. I need the reverse links to all go back to A3 and now they go to A18! :) I only need the forward links to 'smallscroll 15'. Thank you for the great idea!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@tlwellborn 

(Same as Hyperlinks )

 

Select C7 on sheet Dates.

Click in the address box on the left hand side of the formula bar and replace the cell address with a name, for example WorkOrder5 (don't use spaces in the name) and press Enter.

Switch to the sheet with the Work Orders sheet and right-click the cell with the hyperlink.

Select Edit Hyperlink... from the context menu.

You should now see a section Defined Names in the 'Or select a place in this document' tree, with the name that you created under it.

Select it, then click OK.

View solution in original post