Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Houston, Texas, USA
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trace Precedents (2003)

    My formula refers to a cell on a different worksheet in the same spreadsheet. If I click the cell where the formula is, I'd like to be able to change worksheets and have the cell in the other worksheet selected. (Kind of like Trace Precedents, but in another worksheet). Can I do that??

    i.e., I have this formula in Cell B21of the Budget sheet: =Estimate!C29. If I double-click cell B21 in the Budget sheet, I want to click the Estimate sheet tab and cell C29 is highlighted or selected or something.

    What I'm trying to do is check my work without having to hunt down the cells every time I switch back and forth.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Trace Precedents (2003)

    If you add this cell to the worksheet tab, when you dbl-click a cell, if will try to extract the worksheet name and range. If it is valid it will goto the cell and cancel the editing. If it causes an error, it will just go to edit mode (as dbl-click does without the routine)

    <pre>Option Explicit
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim iExcl As Integer
    Dim sWks As String
    Dim sRange As String
    Dim sForm As String

    If Target.HasFormula Then
    sForm = Target.Formula
    iExcl = InStr(sForm, "!")
    If iExcl <> 0 Then
    sWks = Mid(sForm, 2, iExcl - 2)
    sRange = Mid(sForm, iExcl + 1)
    On Error Resume Next
    Application.Goto Worksheets(sWks).Range(sRange)
    If Err = 0 Then
    Cancel = True
    End If
    End If
    End If
    End Sub</pre>


    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trace Precedents (2003)

    Would this tool help? http://www.jkp-ads.com/reftreeanalyser.asp. It has a free demo.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •