How to create dynamic hyperlink based on specific cell value in Excel?
Supposing you have a table in a worksheet is named “Summary”, and you want to create dynamic hyperlink in another worksheet that is linked to the cell value in the table, how can you do? The method in this article will help to solve the problem in detail.
Create dynamic hyperlink based on specific cell value with formula
Take the below screenshot as an example, there is a drop-down list in current worksheet containing all names in column B of the worksheet “Summary”. We need to create a dynamic hyperlink in B3, when selecting a name from the drop-down list, click the hyperlink in B3 will jump to that name cell in worksheet “Summary” immediately.
1. Select a blank cell (here B3) to place the hyperlink, copy and paste the below formula into it and press the Enter key.
=HYPERLINK("#"&CELL("address",INDEX(Summary!B2:B32,MATCH(A3,Summary!B2:B33,0))),"Jump to the data cell")
Summary!B2:B32 means that the dynamic hyperlink will be linked to any cell in range B2:B32 of the worksheet named “Summary”.
A3 is the cell containing the value you will create dynamic hyperlink based on.
“Jump to the data cell” are the displayed texts of the hyperlink cell. Please change them as you need.
From now on, when selecting a name in the drop-down list, and click the hyperlink cell, you can quickly jump to that name cell in the specific worksheet immediately. See the below demo:
Remove hyperlinks keep cell formatting Normally, when deleting hyperlink from a cell with the Remove Hyperlink feature, the cell formatting will be removed with the hyperlink at the same time. If you only want to remove the hyperlink but keep the cell formatting, method in this article will do you a favor.
Combine text and hyperlink into a hyperlinked text in Excel Supposing there are a list of text and a list of hyperlink locating in separate columns, and now, you want to combine the text cell and the hyperlink cell into text that is hyperlinked, how can you do? This tutorial provides method in detail to help you easily achieve it.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.