Static hyperlinks in Excel are links that provide immediate access to another point either to a different route or to a website. Instead, dynamic hyperlinks create a modifiable shortcut to take the user elsewhere. Therefore, if you are interested in knowing how make a dynamic hyperlink in Excel, pay attention to this simple tutorial
How do you create a hyperlink between two files and prevent them from failing when moved?
it’s possible create a hyperlink between two files and prevent them from failing when moved in Excel. Whether you decide to use the Excel desktop app or prefer the mobile app, the procedure to make a dynamic hyperlink is very easy and at the same time productive for your spreadsheets.
In the Excel desktop app
You can create a dynamic hyperlink in the Excel desktop app. you just have to 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 will see that from the ‘Insert’ tab, there is the ‘Hyperlink’ section.
In case you want to add a dynamic hyperlink, you need to use certain functions such as HYPERLINK and MATCH through which you can set a formula where a set of data is taken into account. Therefore, when you click on a link, it will change depending on the selection you have made.
With the mobile app
You can create a hyperlink with the mobile app. In this sense, download and install Microsoft Office from Google Play in order to access different spreadsheets from your Android mobile. You can also use the ‘DocumentsToGo’ tool if you prefer to use an App that takes up less storage space.
What is the way to make a dynamic hyperlink in Excel easily?
Once you download and install Microsoft Excel on your PC or mobile device, you will need to open this app. In a spreadsheet, create a dropdown menu and in a specific cell enter a formula that allows you to reference the workbook, sheet name, column name, add the HYPERLINK and MATCH functions to return a result to the click on the link.
Apply a formula to make a hyperlink to a specific cell
It is possible to apply a make formula to a hyperlink to a specific cell. Suppose we have a dropdown list of names from cells A2 to A20 and we want to add a hyperlink in cell B2 that immediately goes to the value of the list that we have selected. For this, you must use the HYPERLINK formula.
First, you’ll need to go to the specific cell (B2) where the dynamic hyperlink will be placed. Then click on the formula box ‘fx’ and type =HYPERLINK(“#”&CELL(“address”, INDEX(summary!B1:B20,MATCH(A2,summary!B1:B21,0))),”Go to data cell”) and press ‘Enter’.
The previous formula indicates that the hyperlink will be associated with any cell that is in the range between B1 to B20 of the spreadsheet. While A2 is the cell that will take the value to create the dynamic hyperlink. “Go to data cell” is the text that will be displayed in the cell where you will make the hyperlink.
Generate the path of an Excel file in your spreadsheet
it’s possible generate file path from Excel into your spreadsheet. To do this, place your cursor in a cell where you want to create the hyperlink. Likewise, you can select an image, an object or any graphic element with which you can represent the link. Then go to the ‘Insert’ tab and press ‘Links’.
Next, click on the world icon and the chain. Similarly, you can right click on the cell and select ‘Hyperlink’ or press ‘Ctrl + K’. In the ‘Link to’ option click on ‘Press new document’. Then, write the file name in the option ‘Name of the new document’.
You can modify the path shown in ‘Full Path’ before typing the name of the new document by clicking ‘Change’. Choose the location of your preference and press the ‘OK’ button. Select ‘Modify new document later’, change the text to display on the hyperlink and press the ‘OK’ button.
How to move a dynamic Excel file to another PC and not get damaged?
It is possible to move a dynamic Excel file to another PC and prevent it from being damaged. If you have made hyperlinks with the HYPERLINK function, a location or file path dynamically. So, you can place a reference to a cell that has text and modifying said text will modify the reference automatically.
In case you have static links to several files in the same folder, you can try to move them to another folder on the same or another computer; then, you will notice that the hyperlinks will be removed or they will stop working. In such a case, you must use a simplified macro.
First, write the old route and new route in specific cells (A1, A2). Then write these lines to run in the spreadsheet to edit the information about the hyperlink using the replace() function:
Sub hyperbind_change()
Dim hpVinc As Hyperlink
Dim strOldAddress As String, strNewAddress As String
strOldAddress = Range(“A1”)
strNewAddres = Range(“A2”)
For Each hpVinc In ActiveSheet.Hyperlinks
hpVinc.Address = Replace(hpVinc.Address, strOldAddress, strNewAddress)
Next
End Sub
