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
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" <Whooshb...@adelphia.net> wrote in message
news:u4udnQLwzoJ...@adelphia.com...
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" <rjNOr...@PLEASEnetvigator.com> wrote in message
news:ed90x3bK...@tk2msftngp13.phx.gbl...
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" <Whooshb...@adelphia.net> wrote in message
news:5uudnc_if9g...@adelphia.com...
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" <rjNOr...@PLEASEnetvigator.com> wrote in message
news:eY7F13hK...@TK2MSFTNGP14.phx.gbl...
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" <Whooshb...@adelphia.net> wrote in message
news:QYCdnfRG5uJ...@adelphia.com...
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
"Robin Hammond" <rjNOr...@PLEASEnetvigator.com> wrote in message
news:umyH27oK...@TK2MSFTNGP09.phx.gbl...
> 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
D
<jkpie...@netscape.net> wrote in message
news:1111051276.7...@f14g2000cwb.googlegroups.com...