Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Formula auditing (Excel 2003)

    Good morning...can someone tell me how to connect to secondary worksheets when doing formula auditing? I trace precedents (or dependents) and if the source (or target) is on another worksheet, is there an easy way to see what it is? Thank you.....

  2. #2
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula auditing (Excel 2003)

    No, there isn't an easy way to trace the off-sheet precedents of a formula.
    This Link has a Sub (RunMe) that will show all the precedents (on and off sheet) of the Active Cell

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formula auditing (Excel 2003)

    OK...hmm....thank you...I'll take a look at it but I'm not sure that I want to add any more VBA codes etc to the workbook....

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula auditing (Excel 2003)

    You don't need to be as elaborate as that routine.
    The key that I found is that when using the .NavigateArrows method, the ArrowNumber for all off-sheet precedents (dependents) is 1, so you only have to loop through the second index. Note that .NavigateArrows Selects the precedent (dependent) cell.
    The routine in the link is basicaly this (plus bells&whistles and references to closed workbooks. (NavigateArrows only leads to open workbooks))

    <code>Do
    i = i+1
    sourceCell.NavigateArrows(True,1,i)
    MsgBox ActiveCell.Address(,,,True) & " is a precedent of " & sourceCell.Address(,,,True)
    Loop Until ActiveCell.Address(,,,True) = sourceCell.Address(,,,True)</code>



    If I recall correctly, the order that they appear will be their order in the formula (left to right).

    Edit: Oh yeah, sourceCell.TracePrecedents before using .NavigateArrows. Its a screen intensive process, so Application.ScreenUpdating=False is a big time saver.

Posting Permissions

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