At 00:23 19/05/2009 +0100, Harold Fuchs wrote:
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!)
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.
Snap! I had already noticed all of that. No: I don't know why it
happens, either. This clearly requires a deeper understanding than I
have of exactly how formulae are evaluated in a spreadsheet.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
