Precedents & Dependents

[Marian]

Hi,

how can I view ALL Precedents/Dependents in the Sheet by one click? Its
a waste of time to click to every cell with a reference and click icon
Precedents/Dependents...

Any idea?

Thanx

 

[Guest]

Not that I know of, unless with a macro.

Logically, if all cells precendents & dependents can be
displayed by a single click, how will u view your
spreadsheet as it would have been covered with so many
coloured arrows.

 

[Dave Peterson]

I kind of agree with anonymous. I like to show the Formula Auditing toolbar and
just click the icon to show the precedents/dependents.

But if you could live with a small macro, you could select the range you want
(or ctrl-A for the whole sheet) and run it:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select some cells with formulas"
Exit Sub
End If

Application.ScreenUpdating = False

For Each myCell In myRng.Cells
myCell.ShowDependents
myCell.ShowPrecedents
Next myCell

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Marian]

Dave Peterson napsal(a):

I kind of agree with anonymous. I like to show the Formula Auditing toolbar and
just click the icon to show the precedents/dependents.

But if you could live with a small macro, you could select the range you want
(or ctrl-A for the whole sheet) and run it:

Option Explicit
Sub testme01()
.....

Thanx David, it is exactly what I need

Marian