Go to target cell

E

Ed

Hello I have a sheet which is made up of data contained in serveral other
sheets and it is gathered through VLOOKUP and HLOOKUP formulas. If I click on
the cell I can see where the data is coming from, but is it possible to have
something like a double click that takes me to the cell where the data is
entered?

,thanks in advance
 
D

Don Guillett

If you mean you want to goto the lookup table, try this in the sheet module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
On Error Resume Next
ms = Target.Formula
p1 = InStr(ms, ",") + 1
p2 = InStr(ms, "!")
p3 = InStr(ms, ":")
mysheet = Mid(ms, p1, p2 - p1)
mycell = Mid(ms, p2 + 1, p3 - p2 - 1)
Application.Goto Sheets(mysheet).Range(mycell)
End Sub
 
E

Ed

Hello, I tried to insert the code but the first part "Private Sub
Worksheet..." comes out in red when I paste it...
 
D

Don Guillett

Probably word wrap due to email. Goto the end of the top line and use delete
key until the second line comes up.
 
Top