Calling a cell with an embedded hyperlink

Conor365 

To extract the full hyperlink address from the cell and use it in another sheet, you can use a combination of the HYPERLINK, CELL, and INDIRECT functions in Excel.

Here is how you can do it:

Let us assume that in Sheet1, the hyperlinks are in Column A (starting from A2) and the friendly names are in Column B (starting from B2). In Sheet2, you have the lookup value (e.g., "001-500") in cell A2, and you want to retrieve the full hyperlink from Sheet1.

In Sheet2, cell B2, use the following formula:

=HYPERLINK(INDIRECT("Sheet1!"&CELL("address", INDEX(A:A, MATCH(A2, Sheet1!B:B, 0)))), A2)

Here's how the formula works:

1. MATCH(A2, Sheet1!B:B, 0): This finds the row number of the lookup value (e.g., "001-500") in Column B of Sheet1.
2. INDEX(A:A, MATCH(...)): This returns the corresponding cell from Column A of Sheet1 that contains the hyperlink.
3. CELL("address", ...): This returns the cell address of the hyperlink in A2 format (e.g., "$A$5").
4. INDIRECT("Sheet1!"&...): This converts the cell address to a reference in Sheet1.
5. HYPERLINK(..., A2): This creates a clickable hyperlink in Sheet2 using the full hyperlink address from Sheet1 and the friendly name from cell A2 in Sheet2.

By using this formula, you can dynamically retrieve the full hyperlink address from Sheet1 based on the friendly name in Sheet2. You can then drag the formula down to apply it to other rows in Sheet2 for different lookup values.

Please note that this approach assumes that the hyperlinks in Sheet1 are in the format of "https://..." If the format is different, you may need to adjust the formula accordingly. Additionally, the formula assumes that the lookup value in Sheet2 (e.g., "001-500") exactly matches the friendly name in Sheet1. If there are slight variations, you may need to use additional functions like SEARCH or FIND to handle those cases. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.