Forum Discussion
Hyperlink in excel
I want to create a hyperlink from one sheet to another by using data instead of having to use the cell reference. For example in tab 1 I have 2 results for a product such as bread, where one of the 2 results has an error in its setup. So I want to insert a hyperlink using the word “bread” to automatically find “bread” in the second tab without having to enter the cell reference?
1 Reply
Unfortunately, Excel doesn't directly support creating hyperlinks using data within the sheets instead of cell references. However, there are several workarounds you can consider to achieve a similar functionality:
1. Indirect Formula with HYPERLINK:
This method combines the INDIRECT and HYPERLINK functions:
- In Tab 1:
- In the cell where you want the hyperlink, enter the following formula:
Excel=HYPERLINK("#"&INDIRECT("'" & A1 & "'!" & B1), "Bread")
- Replace A1 and B1 with the cells containing the "bread" text and the target cell reference on Tab 2, respectively.
- This formula dynamically constructs the target cell reference based on the values in A1 and B1, creating a hyperlink with the text "Bread."
2. Named Range:
- In Tab 2:
- Select the cell containing "Bread" on Tab 2.
- Go to the Formulas tab and click Define Name.
- Enter a name for the range (e.g., "BreadLink").
- Click OK.
- In Tab 1:
- In the cell where you want the hyperlink, enter the following formula:
Excel=HYPERLINK("#BreadLink", "Bread")
- This formula uses the named range "BreadLink" defined on Tab 2, creating a hyperlink with the text "Bread."
3. VBA Macro:
If you're comfortable with VBA, you can write a macro to search for the text "Bread" on Tab 2 and create a hyperlink accordingly. This offers greater flexibility but requires more technical knowledge.
Choosing the best method:
- Indirect Formula: Simple and efficient for single-cell scenarios.
- Named Range: More manageable for repetitive uses of the same target on different sheets.
- VBA Macro: Most flexible but requires VBA coding skills.
Additional considerations:
- Ensure the target cell on Tab 2 exists and contains the correct data.
- These methods create hyperlinks within the same workbook. If the target is in a different workbook, you'll need to adjust the formulas accordingly.
- Consider the complexity of your data and choose the method that best suits your needs and comfort level.
By implementing one of these workarounds, you can create hyperlinks that dynamically link to specific cells based on data value
- In Tab 1:
