John Heinrich wrote:
>
> 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.
>
>
I'm interested in the question although I can't think of any reason why I
would want to do that. I use Data Ranges more than Named Ranges because I
had problems with the latter initially.
You can assign names to formulas without putting the formulas in cells. For
example, I have a formula named ThisSheet which provides the name of the
active sheet <-- =ThisSheet
You mention the "the 'Insert-Names-All' feature". Is that an innovation
with version 2.1? What does it do?
I would have thought (indeed, been sure) that you could retrieve the
contents of BK2FV by using its cell address with FORMULA, i.e.
=FORMULA(AA23) I do not understand how CELL("CONTENTS";AA23) returns BK2FV
unless BK2FV is the result of the formula in the cell.
I'm winging it here, relying on theory. Surely you can get the cell address
of BK2FV with =CELL("ADDRESS";BK2FV)
As far as I can determine, Help is silent on #NA I'm happy with "not
applicable" or "not available".
--
View this message in context:
http://www.nabble.com/Combining-functions-%22Formula%22--%22Cell%22-tf3178893.html#a8821560
Sent from the openoffice - users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
