Harold Fuchs wrote:
2009/5/19 Don Daugherty <[email protected]>:
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!)
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
I believe that's the beauty of the formula. It executes as soon as it is
entered -- and immediately overwrites itself with the time stamp for that
moment in time. Since the formula has now been replaced by a value, it no
longer changes!
Errrm. No, I disagree. If I invoke the formula at time X, I expect to
get a timestamp reflecting X. That must not change with time. But if I
invoke the same formula again at time Y, in a different cell, I expect
that cell to get a timestamp reflecting time Y. It seems that copying
the formula does not actually "invoke" it in some weird way. If I copy
a "=SUM(..." formula I don't expect the new sum to be the same as the
old one unless the summed values are the same. Instead I expect the
SUM to be invoked on the new range of cells and to reflect the sum of
that range. Why should copying this formula behave differently?
To see my point more clearly, stretch the time. Set the formula in A1
today and save the spreadsheet. Come back a week later and
*copy/paste* the formula into B1. I'd expect B1 to reflect the "week
later" date/time because the formula is being invoked a week later.
The formula *looks* as if it reflects the time it is invoked but ...
Hi Harold
Let me try to explain this. If you put the formula in a cell and
execute it only the date remains (A). If you copy and paste this, you
will get 'A', always. Just like if you type '123' in a cell and copy
and paste it, it will always be '123'. Now if you make a macro of the
formula then when you execute it in a cell, then you will get the
current date/time that will never change, which is what I think you ask
for in the beginning. Also if you copy and paste the formula in will do
the same, but after it is executed you only have the text string that
represents NOW. I do not believe there is anything that you can put in
a cell that shows the date/time of when you inserted it and does not
change with a recalc and you could copy and paste and get a new NOW.
I hope I have made this clearer and not muddier.
Cliff
--
mailto: [email protected]
Clifton R. Liles "Software to the Stars" [email protected]
Pearland, TX 77581 [email protected] [email protected]
- Speaking for myself! Standard disclaimer applies. -
This address may *not* be used for unsolicited mailings.
Failure is not an option. It comes bundled with your Microsoft products.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
