Static hyperlinks in Excel are links that provide immediate access to another location, whether it’s a different path or a website. Dynamic hyperlinks, on the other hand, create a customizable shortcut to take the user to another location. Therefore, if you’re interested in learning how to create a dynamic hyperlink in Excel, check out this simple tutorial.
How to Make a Dynamic Hyperlink in Excel – Simple Tutorial
Index( )
- How do I create a hyperlink between two files and prevent them from failing when moving them?
- In the Excel desktop app
- With the mobile app
- What is the way to make a dynamic hyperlink in Excel easily?
- Apply a formula to make a hyperlink to a specific cell
- Generate the path of an Excel file in your spreadsheet
- How to move a dynamic Excel file to another computer without damaging it?
How do I create a hyperlink between two files and prevent them from failing when moving them?
It’s possible to create a hyperlink between two files and prevent them from failing when moving them in Excel. Whether you choose to use the Excel desktop app or prefer the mobile app, creating a dynamic hyperlink is very easy and also productive for your spreadsheets.
In the Excel desktop app
It’s possible to create a dynamic hyperlink in the Excel desktop app. Simply access the application from the Start menu by opening the “Microsoft Office” folder or directly through the Windows search box. Once you access the app, you’ll see the “Hyperlink” section under the “Insert” tab.
If you want to add a dynamic hyperlink, you’ll need to use certain functions like HYPERLINK and MATCH, which allow you to establish a formula that takes into account a set of data. Therefore, when you click on a link, it will change depending on your selection.
With the mobile app
You can create a hyperlink using the mobile app . Download and install Microsoft Office from Google Play to access different spreadsheets from your Android phone. You can also use the “DocumentsToGo” tool if you prefer an app that takes up less storage space.
What is the way to make a dynamic hyperlink in Excel easily?
Once you’ve downloaded and installed Microsoft Excel on your PC or mobile device, you’ll need to open this app . In a spreadsheet, create a drop-down menu, and in a specific cell, enter a formula that references the workbook, sheet name, and column name. Add the HYPERLINK and MATCH functions to return a result when the link is clicked.
Apply a formula to make a hyperlink to a specific cell
It’s possible to apply a formula to create a hyperlink to a specific cell. Let’s say you have a drop-down list of names from cells A2 to A20, and you want to add a hyperlink in cell B2 that immediately points to the value in the list you selected. To do this, you’ll use the HYPERLINK formula .
First, you’ll need to go to the specific cell (B2) where the dynamic hyperlink will be located. Next, click in the ‘fx’ formula box and type =HYPERLINK(“#”&CELL(“address”, INDEX(summary!B1:B20, MATCH(A2, summary!B1:B21,0))),”Go to data cell”) and press ‘Enter’.
The formula above indicates that the hyperlink will be associated with any cell in the spreadsheet range B1 to B20 . A2 is the cell that will use the value to create the dynamic hyperlink. “Go to data cell” is the text that will be displayed in the cell where you will create the hyperlink.
Generate the path of an Excel file in your spreadsheet
It’s possible to generate the path of an Excel file in your spreadsheet. To do so, place the cursor in a cell where you want to create the hyperlink. You can also select an image, object, or any graphic element to represent the link. Then, go to the ‘Insert’ tab and click ‘Links.’
Next, click the globe and chain icon. You can also right-click the cell and select ‘Hyperlink’ or press ‘Ctrl + K’. Under ‘Link to’, click ‘Click New Document’. Then, type the file name in the ‘New Document Name’ option.
You can modify the path displayed in ‘Full Path’ before entering the name of the new document by clicking ‘Change’. Choose your preferred location and press ‘OK’. Select ‘Modify new document later’, change the text to display in the hyperlink, and press ‘OK’.
How to move a dynamic Excel file to another computer without damaging it?
It’s possible to move a dynamic Excel file to another computer without damaging it. If you’ve created hyperlinks using the HYPERLINK function, a location or path to the file is dynamically created. This way, you can place a reference to a cell that contains text, and when you edit that text, the reference will be automatically modified.
If you have static links to multiple files in the same folder, you can try moving them to another folder on the same computer or to another computer; you’ll then notice that the hyperlinks will be removed or stop working. In this case, you’ll need to use a simplified macro .
First, write the previous and new paths in specific cells (A1, A2). Then, write these lines to run in the spreadsheet to edit the hyperlink information using the replace() function:
Sub hyperlink_change()
Dim hpVinc As Hyperlink
Dim strOldAddress As String, strNewAddres As String
strOldAddress = Range(“A1”)
strNewAddres = Range(“A2”)
For Each hpVinc In ActiveSheet.Hyperlinks
hpVinc.Address = Replace(hpVinc.Address, strOldAddress, strNewAddres)
Next
End Sub
c
