CODEWITHSUNDEEP
excelexcel-formula
Danoz
Danoz

Reputation: 1087

Is it possible to extract cell reference from a hyperlink?

I've been researching this one for a while now with no luck, so thought I would open it up here...

Let's say you have two worksheets in an excel workbook, e.g. sheet1 and sheet2.

Now, in sheet2 cell A1, say you have a hyperlink that refers/points/links to sheet1 cell A1. In other words, the value of the cell reference of the hyperlink at sheet2!A1 is sheet1!A1

Do you know if there is a formula or function that would return the cell reference of that hyperlink.

i.e. 

=<formula-or-function>(sheet2!A1)

which returns 'sheet1!A1' as its result.

Upvotes: 1

Views: 628

Answers (1)

user4039065
user4039065

Reputation:

You can retrieve the .SubAddress from the Hyperlinks.Item Property.

'by the active cell
With ActiveCell
    Debug.Print .Hyperlinks.Item(1).SubAddress
End With

'by worksheet and cell address
With Worksheets("Sheet2").Range("D6")
    Debug.Print .Hyperlinks.Item(1).SubAddress
End With

This is, of course, VBA. I know of no way to perform this action with a worksheet formula short of a User Defined Function (aka UDF) written in VBA.

Upvotes: 1

Related Questions