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

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/E...pandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

"Dean" wrote in message
...
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

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" wrote in message
...
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/E...pandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

"Dean" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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/E...pandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

"Dean" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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/E...pandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

"Dean" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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/E...pandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

"Dean" wrote in message
...
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

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

"Robin Hammond" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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/E...pandXLHome.htm

Robin Hammond
www.enhanceddatasystems.com

"Dean" wrote in message
...
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

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 he

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

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

Sounds like a plan. Thank you!

D

wrote in message
oups.com...
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 he

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

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