Trace Dependents

[Dean]

I have inherited a worksheet that has many rows that are range named. When
I go to a cell in that row and click trace dependents, it shows none, even
though each cell in the row does seem to feed another sheet. Is this the
way it is with range named cells, OR is it only when the range name applies
to multiple cells, OR am I just doing something wrong?

Though I have been told and tried some nice tools created by MVPs, if
possible, I'd like to avoid that, for this aspect, if possible.

Thanks!
Dean

 

[Robin Hammond]

Dean,

it's not as trivial as you might think. The Dependents property in VBA only
gives you dependents on the same sheet. You need to use navigation arrows to
find them across worksheets, then navigate again from the cells you find...
I know you say you don't want to try external tools, but I put a lot of work
into the auditing and tracing routines in my XspandXL add-in and the time
limited trial is fully functional which will get you through your problem.

http://www.enhanceddatasystems.com/ED/Pages/XspandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

 

[Dean]

Excuse my thickness but your 2nd and 3rd sentences confuse me. Unless I am
losing my mind, if you don't use range names, trace dependents gives you all
the dependents both on and off the worksheet containing the cell in
question. Do you agree?

If so, then I can only assume that you are giving me a technical explanation
(which is over my head) as to what EXCEL is capable of, when you use range
names. Is that it?

If so, I think you are telling me that there is no intrinsic way (in EXCEL)
to get around this, short of using clever tools like yours. Is that also
correct?

If so, is yours the one that installs as "name manager". If so, it seems
that when I use it, it is running a macro, though I didn't realize such and
that, every once in awhile, many keystrokes later maybe, I get an error
message that the macro has failed. If so, please explain how to work around
that.

Thanks so much for your patience.
Dean

 

[Robin Hammond]

Dean,

Sorry to have caused some confusion. I just re-read your original question.
I don't see a reason that the original cell is not showing dependents when
you click on the trace dependents command, whether the dependent is refering
to a named range or not. It appears to work fine on XP on my machine. It
should be showing you a diagonal arrow with a small grid at the end, and
when you click on the arrow or grid, the dependent in another sheet shows up
in a small dialog box.

Where I was not clear, and have confused you, is that there is a VBA
property for Dependents of a range that can be used for auditing. However,
this method only returns cells in the same sheet. To find dependents in VBA
in other sheets, you have to use the auditing arrows and navigate along each
external reference which is where it starts to get quite complex.

Where my tool comes in is that it will trace all dependents across all
sheets, and their dependents, to a level that you specify. Same for
precedents. And the same for circular references. i.e. you can trace a full
dependency path from a given cell or range across multiple sheets.

No, mine is not the name manager. That comes from Jan Karel Pieterse. I
haven't experienced any bugs in it and it's been around for a long time
through several builds, but if you are having a problem with it I know from
experience that Jan Karel is both extremely helpful and keen to hear about
it. Mine installs as XspandXL, and like just about all add-ins, contains
macros, some of which run to create menus and toolbars when you load it.

What version of Excel are you running, on what platform? Send me the
workbook if you want (with no macros in it) and I'll have a look and see if
there is a problem on my machine or whether it's a machine specific problem
at your end.

Yours,

Robin Hammond
www.enhanceddatasystems.com

 

[Dean]

Perhaps this is the problem: I think that the cells within the range name
drive only cells that use it within an OFFSET function. And, to make
matters worse, I think the offset function is only an argument within a
CHOOSE function.

I was assuming that these functions were normal EXCEL functions that I just
am not familiar with. Could it be that they are special EXCEL functions
that aren't so transparent? Or could these be some sort of user-defined
function?

I don't know if I can send you this worksheet as I had to sign my life away
to be able to even work on it myself. I would have to sanitize it for
hours. Could you try a range name with either, or a nest, of these two
functions and see if you have the same problem (trace dependent says it has
none)?

Thanks!
Dean

 

[Robin Hammond]

Dean,

I think you've figured it out. I just tried using an offset here with a
normal cell reference and a named cell in Sheet1 and a dependent in Sheet2.
TraceDependents doesn't pick up the dependency of the root cell or the cell
at the offset position. If the precedent and dependent are in the same
sheet, however, it appears to work. Short of writing a dependency tester
from scratch, which would take a while to do, and take ages to run on all
but the smallest of sheets, I don't see a way around this.

The Offset and Choose functions are built in to Excel, not UDFs.

Perhaps somebody else can suggests a workaround?

Robin Hammond
www.enhanceddatasystems.com

 

[Dean]

Is this the first time you've ever heard of this, or is it a well-known
phenomenon for certain types of EXCEL functions? If so, what are the
others?

I guess this also means that the range name had nothing whatsoever to do
with the problem?

If so, this is a bit scary. I am in the habit of deleting cells that have
no dependents, to clean up worksheets. This may need to be a habit I'll
have to break! Yikes!

Dean

 

[jkpieterse]

Hi Dean.

If so, this is a bit scary. I am in the habit of deleting cells that have
no dependents, to clean up worksheets. This may need to be a habit I'll
have to break! Yikes!

What I usually do in such a process is:

- Save all files open (I tend to have external lins involved as well)
- Then delete the range of cells i suspect is unused
- Then run my Flexfind utility to find out whether any #REF! error(s)
have occurred. Flexfind is capable of searching almost any object in
Excel that may have a reference to a cell. Find Flexfind here:

http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.htm

Regards,
Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Dean]

Sounds like a plan. Thank you!

D

Hi Dean.


What I usually do in such a process is:

- Save all files open (I tend to have external lins involved as well)
- Then delete the range of cells i suspect is unused
- Then run my Flexfind utility to find out whether any #REF! error(s)
have occurred. Flexfind is capable of searching almost any object in
Excel that may have a reference to a cell. Find Flexfind here:

http://www.jkp-ads.com/OfficeMarketPlaceFF-EN.htm

Regards,
Jan Karel Pieterse
Excel MVP
www.jkp-ads.com