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: <[email protected]> > 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#a8836421 Sent from the openoffice - users mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
