🚀 go-pugleaf

From article <BAECHLER.92Nov3155440@liasun6.epfl.ch>, by baechler@lia.di.epfl.ch (Emmanuel Baechler):
> 
> I am designing a small planning application with excel. It is intended to
> help people to survey their various tasks, their deadlines, the use of
> their ressources and so on. To do this I absolutely need some way of
> indirect addressing of cells. Indirect adressing is a very common concept
> of programming in "classical" languages like Pascal, C and so on. I will
> however be clearer for people who never used some of these languages: I need
> a way to read and write cells whose address is the content of a given cell,
> and which may be the result of a calculation.
> 

AH!  Indirect addressing in a spreadsheet.  While very useful, it was the
cause of an EXTENDED debugging exercise in my past.  Having wasted all
that time searching for the problem, I feel the need to brag.  Sorry I
can't help with Excel, my experience is entirely LOTUS 1-2-3.  

In Lotus, one gets indirect addressing with the @@ function. If cell J7
contains the label N15 and cell N15 contains the value 1989, then @@(J7) 
returns the value 1989.  (Of course, this works even if J7 is a string
valued function whose value is J7.)

BUT there are subtle "bugs" with this.  Here is the simplest example of an
LOTUS wks which (1) has "natural" recalc, and (2) a cell value is not
correct.  This works with "any" version on Lotus that I know.

Start with an empty wks.  Be sure that the recalc method is natural.  In
cell A1, enter a 4.  In cell A2, enter the label A1.  In cell A3, enter
the formula +@@(A2).  At this point, the value of A3 is (correctly) 4.  Go
to cell A1.  Enter the formula 2*2.  The value of A3 is now 0 and not 4.
WHOOPS, the wks has given the wrong answer, even though recalc is
natural!  You must hit the recalc key to get the right answer in cell A3.
The universe as we know it begins to crumble... 

(Of course, my original problem was with a wks that was HIDEOULSY
complicated.  I still get a headache thinking of that wks.)

LOTUS does NOT consider this a bug.  They have changed their documentation
to say that the cells an indirect reference refers to should NOT be
formulas.  I consider their answer baloney (though in honesty, if you
think about how 123 works internally you will understand that their is no
simple solution to this bug).  

SO, the bottom line of my debugging exercise:  whenever you develop wks
which use @@ functions, be especially careful exactly how those wks are
constructed.  You may want to intentionally want to force the wks recalc
to be "row by row" or "column by column" to ensure that cells which use
indirect addressing's values are correct.  Again, this is the result of
debugging -- the reason why cell A3 has the wrong value in the above is
that internally the formula for A3 is stored before the formula for A1
(since A1's formula was entered last) -- therefore in natural recalc, the
formula for A3 is recalc'd before A1's formula is (and because A3's value
does not OBVIOUSLY depend on cell A1's value).  Fun exercise:  Save this
worksheet without doing an additional recalc to make A3's cell value
correct).  Retrieve the wks.  Explain why the value of A3 is now correct.
(Is it clear that I used to be a college prof?) 

SO, does this help the original post's problem? NO! (probably).  But, I
sure enjoyed sharing what I learned about this....

James H. King
AT&T Bell Laboratories
(908) 949 8908
jhk@hocus.att.com
-- 
James H. King
AT&T Bell Laboratories
(908) 949 8908
jhk@hocus.att.com