How to debug formula problems
 

I have a very strange problem and I need some hint on how to track
this bug down. What I do is to create an XML file in Excel2003
format. Unfortunately the sheet is too complex to post it here, and
I have not managed to reproduce the problem in a smaller test
environment.

Basically all the problems occur in cells like this, all of them are
in worksheet "sheetC":

| =IF(sheetA!$P201,RANK(L201,sheetA:sheetB!$M201,1), "")

Now some of these formulas work fine, others (with different cell
references) don't, without any (noticeable) reaseon. If I play
around a little bit with the formula above, lets say in cell N201, I
can do the following:

The referenced cell L201 contains a formula as well,
=IF(sheetA!$P201,L$194,""), and cell L194 again contains a formula.
If I replace the formula in L194 with the very same value (or any
other value), then the content of L201 immediately does reflect the
content of L194 (which is what anyone would expect), but N201, which
does not even use L194, changes to #INV and tells me that "a value
for the formula or function is not available". In my eyes this is
absolutely IMPOSSIBLE and I do not have any explanation for this
behaviour. Normally, I am quite good in debugging, but here I do
not even know how to start.

If anybody has a hint what to try, please enlighten me. I could send
the XML file per mail, if this is helpful, but I can't show it in
the public (and as I said before, I have not yet managed to create a
similar problem with a neutral sheet).


Bye,
Stefan


--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich

Stefan. Über den bunten Klee zu loben!
(Sloganizer)

How to debug formula problems
 

Send the file to me
add @hotmail.com to to_sheeloo to get my id

"Stefan Froehlich" wrote:

I have a very strange problem and I need some hint on how to track
this bug down. What I do is to create an XML file in Excel2003
format. Unfortunately the sheet is too complex to post it here, and
I have not managed to reproduce the problem in a smaller test
environment.

Basically all the problems occur in cells like this, all of them are
in worksheet "sheetC":

| =IF(sheetA!$P201,RANK(L201,sheetA:sheetB!$M201,1), "")

Now some of these formulas work fine, others (with different cell
references) don't, without any (noticeable) reaseon. If I play
around a little bit with the formula above, lets say in cell N201, I
can do the following:

The referenced cell L201 contains a formula as well,
=IF(sheetA!$P201,L$194,""), and cell L194 again contains a formula.
If I replace the formula in L194 with the very same value (or any
other value), then the content of L201 immediately does reflect the
content of L194 (which is what anyone would expect), but N201, which
does not even use L194, changes to #INV and tells me that "a value
for the formula or function is not available". In my eyes this is
absolutely IMPOSSIBLE and I do not have any explanation for this
behaviour. Normally, I am quite good in debugging, but here I do
not even know how to start.

If anybody has a hint what to try, please enlighten me. I could send
the XML file per mail, if this is helpful, but I can't show it in
the public (and as I said before, I have not yet managed to create a
similar problem with a neutral sheet).


Bye,
Stefan


--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich

Stefan. Über den bunten Klee zu loben!
(Sloganizer)