Forum Discussion
Calling a cell with an embedded hyperlink
In my workbook, I have one sheet (lets say Sheet1) that has a hyperlink associated with each row. The hyperlink is in the format 123-456-789 (friendly name), and the full hyperlink (https://...) is embedded in the cell, so if you click the cell displaying the friendly name, you are brought to the external webpage (https://...). These hyperlinks are copied from an external non-Excel source, so I can't easily extract the full hyperlink (that I am aware of), and the friendly names are part of the data source that copy over automatically.
In my second sheet (Sheet2) I want to VLOOKUP the row number (e.g. 001-500) and return the column with the hyperlink, and make it clickable. Sheet2 is used as a summary sheet that is emailed out for convenient use. The problem is that the HYPERLINK function in excel would look up the text string displayed in Sheet1 (here, 123-456-789) as dumb text and try to convert it to a hyperlink which is not followable.
Is there an easy way to extract the full hyperlink name and use that? Note I do not already have a column of data with the full hyperlinks, and copying these manually over from external data source would be too time intensive, and not maintainable in the future.
2 Replies
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:
- MATCH(A2, Sheet1!B:B, 0): This finds the row number of the lookup value (e.g., "001-500") in Column B of Sheet1.
- INDEX(A:A, MATCH(...)): This returns the corresponding cell from Column A of Sheet1 that contains the hyperlink.
- CELL("address", ...): This returns the cell address of the hyperlink in A2 format (e.g., "$A$5").
- INDIRECT("Sheet1!"&...): This converts the cell address to a reference in Sheet1.
- 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.
NikolinoDE - I adapted the formula (a nice approach for a dynamic search) and unfortunately it's not giving me what I need. It presumes the links in Sheet1 are in the format https:// but unfortunately, they already have friendly names. I'll keep looking! Thanks
