Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Debug on userform | Excel Discussion (Misc queries) | |||
debug code | Excel Discussion (Misc queries) | |||
DeBug | Excel Discussion (Misc queries) | |||
Using the fx key to debug formulas | Excel Discussion (Misc queries) | |||
help with debug | New Users to Excel |