Lookup from hyper link

[boll55]

I have a whole list of hyperlinks that are linked to worksheets in my work book. (Close to 2000)

Is there anyway to create a formula that will look up that worksheet in my workbook and give me selective data?

Thanks


http://www.excelforum.com/excel-formulas-and-functions/938671-lookup-from-hyper-link.html?p=3320446#post3320446

[url]http://www.ozgrid.com/forum/showthread.php?t=180637&p=674137#post674137
[/URL]

 

[Jeffrey Lopez]

How are your hyperlinks set up? What are the name of your "sheets"? are the Sheet1, Sheet2? or different names?

 

[Jeffrey Lopez]

Here is a quick UDF (User Defined Function), that might at least point you in the right direction. This must be placed in a module:

Function HyperLinkValue(rngText As Range) As String


   Dim strSubAdd As String
   
   If rngText.Hyperlinks.Count = 0 Then
      Exit Function
   End If
   
   strSubAdd = rngText.Hyperlinks(1).SubAddress
   HyperLinkValue = Range(strSubAdd)
   
End Function

To use, the Hyperlink must point to the cell that has the data... (I am currently working on getting this to not so much be the case) and you would just use:

 = HyperLinkValue(A2)

 

[boll55]

I used a Macro to collect all the names of my worksheets and combined them into a list. But now I want to pull data from the work sheets without having to go into each one and retrieve it