Nehad Ulfat
In Microsoft Excel, there are several useful and quick methods to add or create a hyperlink to another sheet based on cell value. We can use formulas, functions, or context menus and commands to serve the purposes. In this article, you’ll find all the suitable techniques to add a hyperlink to another sheet based on cell value with proper examples and explanations.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
4 Suitable Methods to Create Hyperlink to Another Sheet Based on Cell Value in Excel
1. Create Hyperlink Based on Cell Value from the Context Menu in Excel
In the following table, we have a list of successive month names starting from January 2021. Now let’s say, we want to create hyperlinks for all months under the Sales Report header. If we click a month name, it’ll redirect to the sales report of that month in another sheet (Sheet2 for January-21).
Here is the second worksheet named Sheet2 where the sales report for January-21 is present.
Now let’s go through the following steps to create or add a hyperlink to the second sheet (Sheet2) in Cell B5 of the first sheet (Sheet1). It means if we click on the text or month name in B5 in Sheet1 then it’ll redirect us to Cell B4 in Sheet2.
📌 Step 1:
➤ In Sheet1, select Cell B5 first.
➤ Right-click your mouse button and open the Context Menu.
➤ Select the last option Link.
A dialog box will appear.
📌 Step 2:
➤ Under the Link to tab, click Place in This Document.
➤ In the ‘Type the cell reference’ box, type B4.
➤ Select Sheet2 from the Cell Reference tab.
➤ Press OK.
Like in the following picture, the hyperlink will be added to Cell B5 in Sheet1. The text in the B5 cell will have an underline. If you move your mouse pointer to the text then it’ll show the address of the corresponding hyperlink and an instructional message.
📌 Step 3:
➤ Click on the text in Cell B5.
And you’ll be redirected to Cell B4 in Sheet2 at once. By following similar procedure, we can create hyperlinks for all months in Sheet1.
Read more: How to Hyperlink to Cell in Excel
2. Use the HYPERLINK function to Add Hyperlink to Another Sheet in Excel
We can also use the HYPERLINK function to create a hyperlink for a cell value in Excel. The generic formula of the HYPERLINK function is:
=HYPERLINK(link_location, [friendly_name])
In the first argument, we have to select the location that the hyperlink will redirect us to. We have to use a Hash symbol (#) before typing the location for the hyperlink. Otherwise, the function will search for another workbook to find the location. The entire first argument needs to be enclosed with the Double-quotes (“ “).
And you have to input the text value in the second argument. This text value will be present on the corresponding cell where the hyperlink will be created.
In the following picture, Sheet1 now has an additional column with the Hyperlinks header. Under this header, we’ll add hyperlinks with our defined text values.
The required formula with the HYPERLINK function in the first output Cell C5 will be:
=HYPERLINK("#Sheet2!B4","Click here")We’re referring the cell value to B4 in Sheet2 here. And the customized message is ‘Click here’.
After pressing Enter you’ll find the defined text with hyperlink as shown in the picture below.
Now click on that hyperlinked text in C5 in Sheet1 and you’ll be redirected to Cell B4 in Sheet2 immediately.
Similar Readings:
- How to Add Hyperlink to Another Sheet in Excel (2 Easy Ways)
- How to Combine Text And Hyperlink in Excel Cell (2 Methods)
- How to Remove External Links in Excel
3. Apply Cell Drag-and-Drop Method to Create Hyperlink to Another Sheet
We have another useful option to create a hyperlink and that is the cell drag-and-drop method. With this method, we can create a hyperlink more easily and within seconds. But to execute this method properly, you have to save the workbook first. Otherwise, this method won’t work out at all.
Now let’s follow the required steps to advance with this method.
📌 Step 1:
➤ Drag your mouse pointer to the cell border of the reference value in a worksheet.
You’ll notice a Plus (+) symbol there.
📌 Step 2:
➤ Right-click your mouse button.
➤ Without releasing the button, hold and drag it to the Sheet name where you want to add the hyperlink.
➤ Once you move your mouse pointer onto the Sheet name, press the ALT button on your keyboard and don’t release it.
You’ll be brought to the corresponding worksheet.
📌 Step 3:
➤ After reaching the corresponding worksheet, now release the ALT button.
➤ Drag your mouse pointer to the cell where you’ll add the hyperlink.
➤ Release the mouse pointer there and the Context Menu will open up.
📌 Step 4:
➤ Choose ‘Create Hyperlink Here’ from the Context Menu and you’re done with the necessary steps.
Finally, like in the following picture, you’ll see the corresponding cell with the hyperlink that you’ve just created.
4. Create Dynamic Hyperlink Based on Cell Value with Combined Formula
In the last section, we’ll create hyperlinks based on the values from a drop-down list. In the picture below, Sheet1 is present where Cell B5 contains a drop-down list with the names of several months. And in Cell C5, we’ll embed a combined formula by using the HYPERLINK, CELL, INDEX, and MATCH functions together.
After creating the hyperlinks, we’ll simply choose a month from the drop-down list and then click on the hyperlinked text in Cell C5 which will redirect us to the reference cell in another worksheet (Sheet2).
The following screenshot represents the second worksheet (Sheet2) where the reference values are lying. The hyperlinks in Sheet1 will switch the window to the reference cell in this second worksheet.
In Sheet1, the required combined formula in the output Cell C5 will be:
=HYPERLINK("#"&CELL("address",INDEX(Sheet2!B5:B13,MATCH(B5,Sheet2!B5:B13,0))),"Click here")
After pressing Enter, you’ll find the hyperlink in the corresponding cell.
Now click on that hyperlink in Cell C5 and you’ll be redirected to Cell B5 in Sheet2 that indicates the sales data for January 2021 in the corresponding row.
You can select any other month (Let’s say it’s June-21 now) from the drop-down list in Sheet1.
And then click on the hyperlink in Cell C5 again.
You’ll be now redirected to Cell B10 in Sheet2 which indicates the sales report for June 2021 in the corresponding row.
Concluding Words
I hope all of these methods mentioned above will now help you to apply them in your Excel workbook when you have to add or create a hyperlink to another sheet. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
