On 18/05/2009 19:34, Brian Barker wrote:
2009/5/14 Nobody Noname:
I want to insert a string into a cell using keystrokes. I'm sorry
this will seem overdone, but I want people to know I've tried to
research this on my own.
[...]
What I want is to do is insert into the active cell a string which
represents 'now', ...
From: Harold Fuchs <[email protected]>
Date: Mon, 18 May 2009 10:06:42 +0100
Sorry to reply to my own post but the simple formula
=IF(A1<>"";IF(B1<>"";B1;NOW());"") entered into cell B1 puts a
non-changing timestamp in B1 if A1 is/becomes not empty but sets B1
empty if A1 is/becomes empty. Sorry but, so far, I can't get B1 to
change if and only if A1 changes.
Oh. To make this work you need to check the "Iterations" box at
Tools>Options>OpenOffice.org Calc>Calculate. If you don't do this
then B1 will show Err 522 - Circular Reference - because the formula
in B1 refers to B1.
Thanks to Winfried Rohr, author of the above mentioned extension, for
this extra goody.
At 08:05 18/05/2009 -0700, Nobody Noname wrote:
Thanks, Harold. I appreciate that you have considered my request to
be worth much of your time :-) .
I'm pretty certain that the recursive formula which you/Winfried Rohr
have offered is a formula that will recalculate when I press F9. I
believe that when I made my first post I mentioned that such a
solution would not be acceptable: "...nor do I want any other
function which will change when F9 is pressed." Further, I titled my
post with the words "insert a date string into a cell".
The reason for this limitation is this: I will use in spreadsheets
whatever solution is successful, those current and future
spreadsheets may need to have several cells calculated; why generate
a spreadsheet in a condition where certain cells must be calculated
yet the cells with date strings must NOT be calculated?
That would represent a major hurdle, in my opinion.
May I say that I am surprised that you should reject this suggested
solution to your problem without even taking the trouble to test it?
(It would take only a moment.) Yes, I'm sure it recalculates when you
ask for that to happen, but it doesn't *change* - which is the
important point. And then you even quote in detail various phrases
from your earlier message!
When you ask a question here, people may try to solve what they
perceive is your problem, even if that involves a technique you hadn't
thought of. Or to offer their best effort, even if it does not
exactly match your conditions, especially where - as here - there may
simply not be a solution in the terms you specify. This is surely
helpful, not something to be dismissed?
Incidentally, I've experimented with this really rather clever
solution, and find that you can simplify the formula a little. To
generate the current date and time in a previously empty cell, use
=IF(xx="";NOW();xx)
- where "xx" is the identifier of the cell in question. As already
explained, you need to have iteration enabled.
I trust this helps. (Oh, but you are welcome to reject it!)
Brian Barker
There's one thing here I don't understand: I enter the formula (either
version) into A1 and get a "now" timestamp. I now wait several minutes.
I now drag/copy A1 across or down. The values produced do *not* reflect
the later time. Instead all the new cells get the same time as was in
the original A1. The same happens if I copy A1 and paste into another
cell. The new cell gets the old time. In both cases the formula is
correctly adjusted to refer to the new column(s)/row(s) but the value
produced reflects the earlier (A1) time. I've tried both Paste and Paste
Special.
Any ideas? Oh: OOo 2.4.1 on Win XP Pro
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
