Formula auditing (Excel 2003)

Topic

New Reply

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…..

Reply | Quote

Replies

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

Reply | Quote

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….

Reply | Quote

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))

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)

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.

Reply | Quote