Finding off-sheet dependents?

[N1GHTEYES]

I know the dependents method does not return any dependents which are on a different sheet, but I need to find if a cell does have any dependents - even if they are on a different sheet.

Does anybody know a way of doing this?

I know Excel must "know" the info, because if I hit the trace dependents button, it does draw an arrow for off-sheet dependents. It will even trace the arrow to the source if you double click it. So how do I persuade Excel to divulge this info?

It is not elegant, but I thought I could use the showdependents method then count the tracer arrows and subtract the number of dependents. That should tell me how many off-sheet dependents there are. Unfortunately I can't find a reference in the range object to its collection of tracer arrows. Does anyone know what this is actually called and how to reference it?

Alternatively, even just knowing that there were off-sheet dependents would be useful, so I thought I'd try running showdependents then navigating the tracer arrow. The idea being that if there were no tracer arrows then trying to navigate one would cause an error. Unfortunately, it doesn't.

So I seem to be in the usual situation of knowing the info is there somewhere, but not being able to figure out how to reference it. Can anybody help?

Thanks,

Tony

 

[SkipVought]

I know of no way to access this hidden Excel content.

You could build you own table of 'parent/child' dependencies for each sheet in the workbook. Something like this to start

Sub deps()
    Dim ws As Worksheet, r As Range
    
    For Each ws In Worksheets
        For Each r In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
            Debug.Print ws.Name, r.Formula
        Next
    Next
End Sub
[/coce]


Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[N1GHTEYES]

Thanks Skip. I considered building my own dependency map, but decided that it would probably be quite slow and, probably too long to code given the time I have available (not much!). I hadn't realised I could use the specialcells / formula approach which might make it a bit faster, but not much, because the other sheets contain a fair few cells with formulae.

I have found a way of identifying if a cell HAS an off-sheet dependency. Count the shapes on it's worksheet, apply the showdependents method, count the shapes again, then remove the arrows. The difference between the two counts is the number of dependency arrows. The difference between that and the number of dependents is the number of off-sheet dependencies. Voila!. Unfortunately it does not tell me where the off-sheet dependencies are - which I need to know.

The other route I thought of was to search each of the other worksheets for the address of the cell being checked. There are only a few hundred of those, so it should be fairly quick.

However, for now I've opted for the very messy but very quick option - I've cut and pasted the content of each of the other sheets to my main sheet, in separate sections below the main area of the main worksheet. Very messy and I hate to do it, but for now it solves the problem quickly.

It really is frustrating though to know that Excel has the info but I just can't get at it. I had hoped it might have been one of those unpublished but useful bits of code that one occasionally hears about here on TT. Oh well, c'est la vie.

Tony