Trace Dependents not working

[Dean]

Though I can't seem to find it, at one point, I learned that EXCEL cannot trace dependents if those dependencies are within the argument of certain EXCEL worksheet functions such as "offset", and a few others. After expressing surprise, some expert later confirmed this.

Today, I am, potentially (though I doubt it) seeing similar behavior with the Cell function, however since it is the W86 cell reference below that says it has no dependents, I don't think that the cell function should matter.

=IF(CELL("TYPE",Override!W45)="V",Override!W45,'Selling & Marketing'!W86/1000)

Do you agree that the cell function should be incidental to this problem, since the worksheet cell in question (W86) is part of the if statement, not the cell function? Or can just having the cell function mixed in there, within the same IF statement, cause EXCEL to not realize the dependency.

What other things can cause the trace dependents to miss finding a dependent (on another worksheet)? By the way, when I do a trace precedents on the cell that is obviously dependent on this cell, it does show up as being its precedent.

Thanks!
Dean

 

[Dean]

<< Though I can't seem to find it >>

To clarify my last post, I meant, "Though I can't seem to find the thread".

Actually, I have now found it - it was elsewhere

http://groups.google.com/group/micr...c9f37c4035d/bb0d315e7c06e25f#bb0d315e7c06e25f

but skip down to my post at 1:46 pm and start reading from there.

Also, I think the aforementioned problem with these functions is limited to when the precedent cell is NOT on the same worksheet - unfortunately, this happens often.

Dean

Though I can't seem to find it, at one point, I learned that EXCEL cannot trace dependents if those dependencies are within the argument of certain EXCEL worksheet functions such as "offset", and a few others. After expressing surprise, some expert later confirmed this.

Today, I am, potentially (though I doubt it) seeing similar behavior with the Cell function, however since it is the W86 cell reference below that says it has no dependents, I don't think that the cell function should matter.

=IF(CELL("TYPE",Override!W45)="V",Override!W45,'Selling & Marketing'!W86/1000)

Do you agree that the cell function should be incidental to this problem, since the worksheet cell in question (W86) is part of the if statement, not the cell function? Or can just having the cell function mixed in there, within the same IF statement, cause EXCEL to not realize the dependency.

What other things can cause the trace dependents to miss finding a dependent (on another worksheet)? By the way, when I do a trace precedents on the cell that is obviously dependent on this cell, it does show up as being its precedent.

Thanks!
Dean

 

[Jim Rech]

I reproduced your issue and I also see that with a formula that does not
include Cell("Type") like =SellingMarketing!W86/1000 this is not a problem.
So it seems that the entire formula is tainted by Cell("Type"). I didn't
know about this behavior and it's surprising. The only workaround I can
suggest is using another function, like ISTEXT, if you can find one that
does what you want.

--
Jim
<< Though I can't seem to find it >>

To clarify my last post, I meant, "Though I can't seem to find the thread".

Actually, I have now found it - it was elsewhere

http://groups.google.com/group/micr...c9f37c4035d/bb0d315e7c06e25f#bb0d315e7c06e25f

but skip down to my post at 1:46 pm and start reading from there.

Also, I think the aforementioned problem with these functions is limited to
when the precedent cell is NOT on the same worksheet - unfortunately, this
happens often.

Dean

Though I can't seem to find it, at one point, I learned that EXCEL cannot
trace dependents if those dependencies are within the argument of certain
EXCEL worksheet functions such as "offset", and a few others. After
expressing surprise, some expert later confirmed this.

Today, I am, potentially (though I doubt it) seeing similar behavior with
the Cell function, however since it is the W86 cell reference below that
says it has no dependents, I don't think that the cell function should
matter.

=IF(CELL("TYPE",Override!W45)="V",Override!W45,'Selling &
Marketing'!W86/1000)

Do you agree that the cell function should be incidental to this problem,
since the worksheet cell in question (W86) is part of the if statement, not
the cell function? Or can just having the cell function mixed in there,
within the same IF statement, cause EXCEL to not realize the dependency.

What other things can cause the trace dependents to miss finding a
dependent (on another worksheet)? By the way, when I do a trace precedents
on the cell that is obviously dependent on this cell, it does show up as
being its precedent.

Thanks!
Dean

 

[Dean]

Apparently, lots of experts don't know about this and the help section
within EXCEL does not mention it. Does anyone here have contacts at
Microsoft to get them to fix this kind of thing?

I bet that there are a host of functions that have this problem - "offset"
is one of them, as I recall, "choose" is another. Frankly, I don't use many
of these fancy new functions (though I do see they are quite valuable) and
choose to program the old fashioned (call it tedious) way. However, I am
asked to modify other people's files and that's where I do see a lot of
this.

It's very disturbing to me, particularly the mere "taint" (guilt by
association) to which you refer!

Thanks, Jim.

D