I have a large spreadsheet in OO-Calc & Windows XP that contains a large number
of calculations in cells that have been given names. The names "work"; I use
them to refer to the calculation result in subsequent calculations.
I need to make a list of the cell names and their formulae. Several other
spreadsheet programs have easy ways to do this; I can't find such capability in
OO-Calc. Solving this is problem one. In attempting a work-around, I have used
the "Insert-Names-All" feature to make a list of cell names. Also, the function
FORMULA(cellRef)
will return a string expression of the formula contained in the referenced
cell. My plan: to construct a working function, and then copy it down a column
adjacent to the column of cell names to get a list of corresponding functions.
Thus arose problem 2.
BK2FV is the name of a cell containing an arithmetic function. If I enter
=FORMULA(BK2FV), it properly returns the formula contained in BK2FV. But using
that would require handwriting every expression. However, BK2FV is in cell AA23
in the list of cell names. If in an adjacent cell I write
CELL("CONTENTS";AA23), it returns BK2FV. However, when I combine this with
FORMULA(CELL("CONTENTS";AA23)), (expecting the CELL function to supply BK2FV to
the formula function), I get #N/A returned. That led to problem 3. I can't find
an explanation of error code #N/A.
Another funny thing. If I use string concatenation to assemble =FORMULA(BK2FV),
and then COPY>PASTE-SPECIAL to convert it to a proper string, it looks for the
outside world just like my hand-written version that worked, but this one won't
compute.
Any Suggestions on any of these problems, or for making a column of cell names
with their contained functions? John.
