Aug 09 2021 10:24 AM
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?
Aug 09 2021 10:43 AM
Solution(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.
Jan 11 2023 10:27 AM
Jan 11 2023 12:06 PM
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
Jan 11 2023 01:28 PM
Aug 09 2021 10:43 AM
Solution(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.