Harold Fuchs wrote:
On 20/05/2009 20:08, Clifton Liles wrote:
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).
Sorry to be blunt but I believe this is complete nonsense. If I put a
formula "=sum(A1:A4)" into A5, and if A1 to A4 contain 1, 2, 3, 4,
then I get 10 in A5. If I change A3, then a5 changes. This is because
A5 does *not* contain 10. It contains the formula. Similarly, if I
copy/paste from A5 to B5 and if B1 to B4 contain 10, 20, 30, 40 then
B5 gets 100 because the copy/paste invokes the formula in B5 having
adjusted the "A1:A4" to read "B1:B4".
If I put into A1 the formula "=if(A1=0;NOW();A1)" then, if A1 is zero
I get the current time else I get whatever was in A1. But I don't get
"the current time". I get the formula. Check it for yourself. Put the
formula into A1 and hit Enter. Now click back into A1 and look at the
data entry line. You'll see the formula, *not* the date/time. So when
I copy that to B1 5 minutes later I should *not* get A1's time but the
new "5 minutes later" time. Because I've copied/pasted the *formula*.
Check it, B1 will contain "=IF(B1=0;NOW();B1)" and that "NOW() should
evaluate to the 5-minutes-later time.
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'.
No. If I enter "123" and copy/paste it I'll get "123". But if A1 has
123 and I put into B1 "=A1" then B1 will get 123. If I put 235 into C1
and copy/paste B1 to D1 then D1 will get 235. Try it. The reason is
that (a) it's the formula that gets pasted, not the value and (b) the
formula that gets pasted into D1 gets adjusted to read "=C1". If your
"logic" held sway, D1 would contain 123 and that would break 99.9% of
all spreadsheets ever built since 1979 when Visicalc made its first
appearance.
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.
Wrong. You have the formula, *not* the text string; same as
copy/pasting any other formula. Try it and look at the results.
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.
Clearer but wrong! Or, in the immortal words of Wolfgang Pauli "not
even wrong".
No. There's something else going on here which I don't understand.
Cliff
Hi Harold
OK, I'm wrong. I thought I saw the problem, but did not. What we need
is a function that puts the results of on 'NOW()' in the cell. I do not
know how to do that in Calc. It just died when I ask for the help for
'current function'. I 'll leave this to someone who does understand
Calc.
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]
