Excel Formula

[JonSmile]

But if you turn off autocalc, it will cause confusion to the poor end user. Think of the confusion and support calls you will generate (or even worst if they do not notice!) when their reports do not balance.

IMHO the VBA mentioned above is a better option as it means that the poor user cannot overwrite your code (think about users doing cut /paste and messing up the hardcoded formula in the cell).

As the VBA is forcing the value of the cell to be the date and not a formula, you do not have to worry about autocalc.

Whenever I am doing anything like this I always go for the route that cuts down the users ability to mess it all up!

Jon

[Board Game Geek]

Bear in mind that every time you open the spreadsheet, or press F9 tho, the formula will recalculate and display the current date (rather than the date that the cell was changed)

Then turn autocalculation off.

Tools / Options / General (i think)

Tickbox to turn on or off or do manually with F9.

[MrRobin]

Thanks for help

I went with a quick fix. If statement that when a cell doesn't match a certain value it populates another cell with TODAY(). when it does match the certain value then it displays nothing (blank)

It works and is what i wanted

Bear in mind that every time you open the spreadsheet, or press F9 tho, the formula will recalculate and display the current date (rather than the date that the cell was changed)

[Cooperinliverp00l]

rather than a macro you could link a1 to a2 then compare values and make a3 = now() or soemthing like that when the two cells aren't a match... obviously using IF and hiding a2 etc...

you get the drift... depends on the macro security setting of the proposed target spreadsheet consumer i guess....

Thanks for help

I went with a quick fix. If statement that when a cell doesn't match a certain value it populates another cell with TODAY(). when it does match the certain value then it displays nothing (blank)

It works and is what i wanted

[kramer]

rather than a macro you could link a1 to a2 then compare values and make a3 = now() or soemthing like that when the two cells aren't a match... obviously using IF and hiding a2 etc...

you get the drift... depends on the macro security setting of the proposed target spreadsheet consumer i guess....

[sli_gryn]

there's the "NOW()" function which gives you a time/date stamp

all you have to do is wrap an "onchange" event to the cell you want,
and then trigger a NOW() population to the target

something like...

'put this anywhere
sub onchange(ByVal target as Range)
target.offset(0,1).value = now() 'this sets the cell to the right to the time
end sub

'put this against the worksheet
'it picks up a specific cell and detects against it (A1)
sub Worksheet_Change(ByVal target As Range)
If target.Address = "$A$1" And target.Value > 0 Then
Call onchange(target) 'this calls the sub above and parses in the cell
End If
end sub

it's a rough one, but might help you get to the next step

[Cooperinliverp00l]

Calling all Excel Experts

I have a shared spreadsheet that i want to add a little bit of functionality to it. when i update cell with a value i want the next cell to display the Date on which it was updated. Does excell have a get date function ?

Please help