Thanks for your efforts. On the 'Insert-Names-All feature' you asked about:
I use cell names a lot and find this essential to debugging.
Place the cursor where you want the list of names to begin. Then from the
main menu:
Select INSERT
then select Names
Then select Insert
Then select Insert All.
Lo and Behold, a vertical list of cell names with their cell addresses.
----- Original Message -----
From: "TerryJ" <[EMAIL PROTECTED]>
To: <users@openoffice.org>
Sent: Monday, February 05, 2007 11:26 PM
Subject: Re: [users] Combining functions "Formula"& "Cell"
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
