Excel Hyperlink to Another Sheet Based on Cell Value

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).

Create Hyperlink Based on Cell Value from the Context Menu in Excel

Here is the second worksheet named Sheet2 where the sales report for January-21 is present.

Create Hyperlink Based on Cell Value from the Context Menu in Excel

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.

Create Hyperlink Based on Cell Value from the Context Menu in Excel

📌 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.

Create Hyperlink Based on Cell Value from the Context Menu in Excel

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.

Create Hyperlink Based on Cell Value from the Context Menu in Excel

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.

Create Hyperlink Based on Cell Value from the Context Menu 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.

Use the HYPERLINK function to Add Hyperlink to Another Sheet in Excel

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’.

Use the HYPERLINK function to Add Hyperlink to Another Sheet in Excel

After pressing Enter you’ll find the defined text with hyperlink as shown in the picture below.

Use the HYPERLINK function to Add Hyperlink to Another Sheet in Excel

Now click on that hyperlinked text in C5 in Sheet1 and you’ll be redirected to Cell B4 in Sheet2 immediately.

Use the HYPERLINK function to Add Hyperlink to Another Sheet 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.

Apply Cell Drag-and-Drop Method to Create Hyperlink to Another Sheet

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.

Apply Cell Drag-and-Drop Method to Create Hyperlink to Another Sheet

📌 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.

Apply Cell Drag-and-Drop Method to Create Hyperlink to Another Sheet

📌 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.

Apply Cell Drag-and-Drop Method to Create Hyperlink to Another Sheet

📌 Step 4:

➤ Choose ‘Create Hyperlink Here’ from the Context Menu and you’re done with the necessary steps.

Apply Cell Drag-and-Drop Method to Create Hyperlink to Another Sheet

Finally, like in the following picture, you’ll see the corresponding cell with the hyperlink that you’ve just created.

Apply Cell Drag-and-Drop Method to Create Hyperlink to Another Sheet


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).

Create Dynamic Hyperlink Based on Cell Value with Combined Formula

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.

Create Dynamic Hyperlink Based on Cell Value with Combined Formula

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")

Create Dynamic Hyperlink Based on Cell Value with Combined Formula

After pressing Enter, you’ll find the hyperlink in the corresponding cell.

Create Dynamic Hyperlink Based on Cell Value with Combined Formula

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.

Create Dynamic Hyperlink Based on Cell Value with Combined Formula

You can select any other month (Let’s say it’s June-21 now) from the drop-down list in Sheet1.

Create Dynamic Hyperlink Based on Cell Value with Combined Formula

And then click on the hyperlink in Cell C5 again.

Create Dynamic Hyperlink Based on Cell Value with Combined Formula

You’ll be now redirected to Cell B10 in Sheet2 which indicates the sales report for June 2021 in the corresponding row.

Create Dynamic Hyperlink Based on Cell Value with Combined Formula


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.


You May Also Like to Explore

Find Broken Links in Excel (4 Quick Methods)

Find External Links in Excel (6 Quick Methods)

How to Combine Text And Hyperlink in Excel Cell (2 Methods)

How to Edit Links in Excel (3 Methods)

How to Remove External Links in Excel

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo