John Heinrich wrote:
... If in an adjacent cell I write CELL("CONTENTS";AA23), it returns
BK2FV. However, when I combine this with
FORMULA(CELL("CONTENTS";AA23)), ... I get #N/A returned.
I think the right thing to use here is "INDIRECT()", the purpose of
which is to convert a cell containing a string (a cell address) into the
actual reference. Named ranges function as cell addresses, so if A1=123
and A2="A1", INDIRECT(A2) gives you 123. Or in your example, if
A2="BK2FV" and "BK2FV" is a name for A1, then INDIRECT(A2) should give
123 as well.
Unfortunately, it doesn't. It gives #ERR 502 (Invalid argument).
And lo and behold:
INDIRECT function fails with named cells
http://qa.openoffice.org/issues/show_bug.cgi?id=4695
That report is now almost five years old and has 24 votes, however
someone has volunteered to try and fix it. Hopefully that will bear
fruit soon.
... That led to problem 3. I can't find an explanation of error code
#N/A.
I've wondered about this myself. The help for ISNA says:
Returns TRUE if a cell contains the #N/A (value not available)
error value.
However, the #N/A error also has special significance for other functions.
I think the real answer is that this is just a generic error: it means
different things at different times. That doesn't mean that it shouldn't
be described in the table along with the others. I'd report this as a
documentation bug (it's not there that I can find).
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.
Not exactly sure, but I've seen this happen. I think when you do "Paste
Special" and specify "string" but not "formula", you get strings and NOT
formulas. I.e., I don't think you can use "Paste Special" to convert a
string to a formula. I often find that I have to copy the string (that
looks like a formula) to the clipboard, clear the formula entry, and
paste it again as if I had typed it.
Any Suggestions on any of these problems, or for making a column of
cell names with their contained functions? John.
Not at all, short of writing a macro. I tried recording a macro but it
isn't useful because all the cell references it records are absolute: it
would be useless for more than one cell. I don't know Calc macros well
enough to make it more generic.
Calc seems to have carefully walled off what you want to do.
<Joe
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
