How to add the current date to a cell.?

[Moradyn]

If A1 is blank, and A2=blank and a3= Now()

When i enter the name 'Steve' into A1, i want cell A2 to enter the current date (and stay that way forever)


Anyone know how ?

 

[Chris Davison]

Dan,

some beans, M'Lord....

well yeah, sort of : it's just whenever I read "impossible" in an excel-related issue, I refuse to believe it (within reason, obviously) and save it for a rainy day when I'm bored.

although solving this and winning your tenner could be "stickier than a stick insect who's particularly stuck etc etc etc"

:wink:

 

[dk]

Chris my man,

I agree with you. No-one should say 'it can't be done' unless they're sure that they're right. OK, maybe I'm being a bit ****y but I will gladly hand you a tenner and then buy you a large quantity of Stella/Grolsch/meths if you can achieve this. I would like a formula which can insert a random number into a cell which once entered will never change.

Looking forward to buying you those drinks

Dan

 

[Chris Davison]

On 2002-04-15 15:47, dk wrote:
I would like a formula which can insert a random number into a cell which once entered will never change.

Looking forward to buying you those drinks

Dan

Dan

:wink:

what population size should this random number come from ?

(ie 0 to 1, or 1 to 1,000 etc etc ?)

Also, do you want decimals or just integers ?

Chris

 

[dk]

Chris,

Either would be fine. I currently use =RAND() and then copy, paste special as values. Not really much of a chore but I'd be interested to see if there is a way of using a formula which won't change every time the worksheet is calculated.

Regards,
Dan

 

[nisht]

write down this procedure in workbook open event.

first you need to put formula in a3 for current date (=today())

sub stevedate ()

if [a1] = "steve" and isnull[a2] then
' if [a1] = "steve" and not isdate[a2] then
[a2] = vba.date
end if
end sub

ni****h desai
http://www.pexcel.com

 

[Chris Davison]

On 2002-04-18 06:53, dk wrote:
Chris,

Either would be fine. I currently use =RAND() and then copy, paste special as values. Not really much of a chore but I'd be interested to see if there is a way of using a formula which won't change every time the worksheet is calculated.

Regards,
Dan

Evening Dan,

replicating the output of a bare =RAND() you could try this in a cell :

=RIGHT((CALL("Kernel32","GetTickCount","J")^0.5)^0.5,5)/100000

this'll give you (in my opinion) as near a random number between 0 and 1 as is conceivably possible and unpredictable which won't change with a recalc.

Copy the formula somewhere else and you get a different result each time..... random numbers that don't change.

The purists, within which I'd include myself, would argue it's not random as it's based on some internal something or other inside the computer. But hey, if anyone can predict the random number that their computer will give when they copy this into their worksheet, then I'll eat my hat.

To make it volatile, replace the "J" with "J!"

Spookey, eh ?

Chris

 

[dk]

Chris,

Sorry it's taken me a while to reply - busy, busy, busy

Anyway, I'm very impressed by the ingenuity of your solution and guess by rights that I now owe you cash/beer. For my purposes the number would definitely be random enough.

However, there is a slight problem. The CALL function was disabled in Excel 2000 http://support.microsoft.com/default.aspx?scid=kb;en-us;Q238996 so when I paste this formula in I just get 0.0000000000000. I guess the way around this is to use your method within a VBA UDF and then call that on the worksheet which I'll have a look at shortly.

Regards,
Dan
This message was edited by dk on 2002-04-23 05:58

 

[Chris Davison]

Dammit !!

actually I later noticed it did recalc when rows or columns were inserted or deleted, but not on other normal recalcs...weird

so no worries on the beer if it's also not supported in later versions (I still use '97, when DReam were No1 and the Tories were getting their arses rightfully kicked)

Happy Days !!!

The tenner was for the date thing, so that's still open, for now.......

*cue sinister B-Movie music*

Dun Dun Duhhhhnnnnnn....

 

[Mark O'Brien]

The purists, within which I'd include myself, would argue it's not random as it's based on some internal something or other inside the computer. But hey, if anyone can predict the random number that their computer will give when they copy this into their worksheet, then I'll eat my hat.

That sounds like a challenge. Get your hat ready. or would you prefer some jellied eels?