If your named cell contains something other than a formula:
=CELL("contents";INDIRECT(MID(F72;2;256)))
If you want one formula to suit all, you could use IF(ISNA(FORMULA( etc
)));CELL( etc );FORMULA( etc )))) =-O
TerryJ wrote:
>
> Thanks for that. In the list created I have the following in E72:F72, the
> first being the name, the second the cell reference: calcNSheet
> =$B.$A$69
>
> In G72, I place the following formula: =FORMULA(INDIRECT(MID(F72;2;256)))
>
> That gives me the contents of calcNSheet. As Joe Smith says, INDIRECT
> will not use a range name.
>
> I'm not sure whether that leaves any question unanswered.
>
> HTH.
>
>
> John Heinrich wrote:
>>
>> 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]
>>
>>
>>
>
>
--
View this message in context:
http://www.nabble.com/Combining-functions-%22Formula%22--%22Cell%22-tf3178893.html#a8836662
Sent from the openoffice - users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
