Clicking on linked cells

[PT]

Hi,

Does anyone know if there is a function or macro for Excel 2003 that will
allow you to double click in a cell, which pulls data from another cell
within the workbook (either on the same worksheet or a different one), and
have it take you to the cell which is linked to the cell you are clicking on?
does that make any sense?

for example, if I am clicking on cell A1, which is showing the value $100,
but the $100 is being pulled from cell A1 on another worksheet within the
workbook, I want to be able to go to the other worksheet, and have my cursor
be in cell A1 in that worksheet. I want to be able to do this for any cell
within the workbook that is linked.

At my previous job, the Excel software they provided for use had this
functionality, but I can't do this on the current version of Excel I have at
my new job, and it's driving me nuts.

Please help. Thanks!

 

[www.exciter.gr: Custom Excel Functions!]

I suggest you insert the following code into the sheet's (the one
where you doubleclick) vba window:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim C2S As String
If InStr(1, Target.Formula, "1") > 0 Then
Sheets(Replace(Left(Target.Formula, InStr(1, Target.Formula, "!") -
1), "=", "")).Select
C2S = Right(Target.Formula, Len(Target.Formula) - InStr(1,
Target.Formula, "!"))
ActiveSheet.Range("A1").Select
Cancel = True
End If
End Sub

When you double click on a cell of this sheet, the above code searches
the cell's formula, finds the refering sheet and refering cell and
move the cursor there. It will work ONLY if all references are inside
the same workbook, and ONLY if the cell you double click has plain
reference to another cell, not calculations. For example it works for
a formula reference like "=Sheet1!A3" and does not work if the formula
is like "=SUM(sheet1!A1:A10), or like "=Sheet1!A3*10".

Hope this helps!

http://www.exciter.gr
Custom Excel Applications and Functions

 

[Max]

Another play to try ..

1. Install the sub ClearArrows() below into a regular module
Press Alt+F11 to go to VBE
Click Insert > Module
Copy n paste the code below into the whitespace
Press Alt+Q to get back to Excel

Sub ClearArrows()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
s.ClearArrows
Next
End Sub

2. Install the sub below into the sheet
Right-click on the sheet tab > View Code
Copy n paste the code below into the whitespace
Press Alt+Q to get back to Excel

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Selection.ShowPrecedents
ActiveCell.NavigateArrow TowardPrecedent:=True, _
ArrowNumber:=1, LinkNumber:=1
ClearArrows
End Sub

Repeat step 2 above to install into each sheet as required

Test it out .. Eg: If installed into Sheet3, when you double-click on a cell
with a link formula, say in A1: =Sheet2!B3, it will jump you to that
precedent ie to Sheet2's B3