Refreshing one cell
 

Hello,

I'm not too proficient with Excel so hopefully this can be done. My client
is using this formula to update the date and time in a particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any cell
is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Hi,

You could do this. Right click your sheet tab, view code and paste this in.
It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client is using this formula to update the date and time in a particular
cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

This may be awkward for the client and before I do this, I'd like to ask
another question.

I tried modifying the formula using the And operator to test the value of
the cell containing the formula,

=IF(A6="" and B6<"","",NOW())

Excel didn't like it. Am I referencing the cells incorrectly or is this
just an illegal operation?

God Bless,

Mark

"Mike H" wrote in message
...
Hi,

You could do this. Right click your sheet tab, view code and paste this
in. It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client is using this formula to update the date and time in a particular
cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Mark

Couple of methods on John McGimpsey's site

One without VBA and one with.

http://www.mcgimpsey.com/excel/timestamp.html


Gord Dibben MS Excel MVP

On Fri, 20 Mar 2009 15:17:41 -0400, "Mark A. Sam"
wrote:

Hello,

I'm not too proficient with Excel so hopefully this can be done. My client
is using this formula to update the date and time in a particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any cell
is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Hi,

Excel won't like that because the correct syntax is

=IF(AND(A6="",B6<""),"",NOW())

But you will still have the same problem of NOW() updating every time the
worksheet calculates as soon as those 2 conditions evaluate as TRUE

Mike
"Mark A. Sam" wrote in message
...
This may be awkward for the client and before I do this, I'd like to ask
another question.

I tried modifying the formula using the And operator to test the value of
the cell containing the formula,

=IF(A6="" and B6<"","",NOW())

Excel didn't like it. Am I referencing the cells incorrectly or is this
just an illegal operation?

God Bless,

Mark

"Mike H" wrote in message
...
Hi,

You could do this. Right click your sheet tab, view code and paste this
in. It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client is using this formula to update the date and time in a particular
cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell is updated. Does this require a code and if so how to implement
it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Gord.

What he is looking to do is to copy the the formula from one cell to
another. After seeing how it works, I think the coding option may be to
cumbersome for him. It will require modifying the code for each cell he
wants to target, at least that is how it seems to me. I used on cell as an
example, but he will be addressing many cells. Am I wrong in my thinking?

What I think I need to do is modify the formula something like this:

=IF(A6="" and B6<"","",NOW())

But that doesn't work. It is illegal syntax.

God Bless,

Mark




"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mark

Couple of methods on John McGimpsey's site

One without VBA and one with.

http://www.mcgimpsey.com/excel/timestamp.html


Gord Dibben MS Excel MVP

On Fri, 20 Mar 2009 15:17:41 -0400, "Mark A. Sam"
wrote:

Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client
is using this formula to update the date and time in a particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell
is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Did you try the circular reference formula from John's site?

=IF(A1="","",IF(B1="",NOW(),B1))

Of course, after changing the Iterations setting.


Gord

On Fri, 20 Mar 2009 15:54:40 -0400, "Mark A. Sam"
wrote:

Gord.

What he is looking to do is to copy the the formula from one cell to
another. After seeing how it works, I think the coding option may be to
cumbersome for him. It will require modifying the code for each cell he
wants to target, at least that is how it seems to me. I used on cell as an
example, but he will be addressing many cells. Am I wrong in my thinking?

What I think I need to do is modify the formula something like this:

=IF(A6="" and B6<"","",NOW())

But that doesn't work. It is illegal syntax.

God Bless,

Mark




"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Mark

Couple of methods on John McGimpsey's site

One without VBA and one with.

http://www.mcgimpsey.com/excel/timestamp.html


Gord Dibben MS Excel MVP

On Fri, 20 Mar 2009 15:17:41 -0400, "Mark A. Sam"
wrote:

Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client
is using this formula to update the date and time in a particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell
is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Mike,

=IF(AND(A6="",B6<""),"",NOW()) gave me circular reference problem. The
subroutine you gave me also refreshed the other cells. Maybe becuase the
worksheet automatically refreshes whenever a cell is updated?



"Mike H" wrote in message
...
Hi,

Excel won't like that because the correct syntax is

=IF(AND(A6="",B6<""),"",NOW())

But you will still have the same problem of NOW() updating every time the
worksheet calculates as soon as those 2 conditions evaluate as TRUE

Mike
"Mark A. Sam" wrote in message
...
This may be awkward for the client and before I do this, I'd like to ask
another question.

I tried modifying the formula using the And operator to test the value of
the cell containing the formula,

=IF(A6="" and B6<"","",NOW())

Excel didn't like it. Am I referencing the cells incorrectly or is this
just an illegal operation?

God Bless,

Mark

"Mike H" wrote in message
...
Hi,

You could do this. Right click your sheet tab, view code and paste this
in. It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client is using this formula to update the date and time in a
particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell is updated. Does this require a code and if so how to implement
it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

It is getting there, but not quite. I placed the formula into cell B1, but
it when I update A1, it receives that date (20/3/2009 16:21) and B1
receives an unknown value (39892.68145).


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Did you try the circular reference formula from John's site?

=IF(A1="","",IF(B1="",NOW(),B1))

Of course, after changing the Iterations setting.


Gord

On Fri, 20 Mar 2009 15:54:40 -0400, "Mark A. Sam"
wrote:

Gord.

What he is looking to do is to copy the the formula from one cell to
another. After seeing how it works, I think the coding option may be to
cumbersome for him. It will require modifying the code for each cell he
wants to target, at least that is how it seems to me. I used on cell as
an
example, but he will be addressing many cells. Am I wrong in my thinking?

What I think I need to do is modify the formula something like this:

=IF(A6="" and B6<"","",NOW())

But that doesn't work. It is illegal syntax.

God Bless,

Mark




"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Mark

Couple of methods on John McGimpsey's site

One without VBA and one with.

http://www.mcgimpsey.com/excel/timestamp.html


Gord Dibben MS Excel MVP

On Fri, 20 Mar 2009 15:17:41 -0400, "Mark A. Sam"
wrote:

Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client
is using this formula to update the date and time in a particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell
is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Hi,

It gave a circular reference because you put it in either A6 or B6, you
never got around to telling us which cell you wanted the date/time in. No
matter how long you play with this unless you use the circular reference
method given to you by Gord you won't achieve what you want using a formula.
If you want to use code then tell us the range of cells to look at and where
to put the date & time.

Mike

"Mark A. Sam" wrote in message
...
Mike,

=IF(AND(A6="",B6<""),"",NOW()) gave me circular reference problem. The
subroutine you gave me also refreshed the other cells. Maybe becuase the
worksheet automatically refreshes whenever a cell is updated?



"Mike H" wrote in message
...
Hi,

Excel won't like that because the correct syntax is

=IF(AND(A6="",B6<""),"",NOW())

But you will still have the same problem of NOW() updating every time the
worksheet calculates as soon as those 2 conditions evaluate as TRUE

Mike
"Mark A. Sam" wrote in message
...
This may be awkward for the client and before I do this, I'd like to ask
another question.

I tried modifying the formula using the And operator to test the value
of the cell containing the formula,

=IF(A6="" and B6<"","",NOW())

Excel didn't like it. Am I referencing the cells incorrectly or is this
just an illegal operation?

God Bless,

Mark

"Mike H" wrote in message
...
Hi,

You could do this. Right click your sheet tab, view code and paste this
in. It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client is using this formula to update the date and time in a
particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell is updated. Does this require a code and if so how to implement
it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Mike,

He is using Column A to enter data and wants the corresponding cell in
Column B updated with the Date/Time:

A1 B1
..53 3/20/2009 14:00
..4 3/20/2009 14:21

Does that make sense?




"Mike H" wrote in message
...
Hi,

It gave a circular reference because you put it in either A6 or B6, you
never got around to telling us which cell you wanted the date/time in. No
matter how long you play with this unless you use the circular reference
method given to you by Gord you won't achieve what you want using a
formula. If you want to use code then tell us the range of cells to look
at and where to put the date & time.

Mike

"Mark A. Sam" wrote in message
...
Mike,

=IF(AND(A6="",B6<""),"",NOW()) gave me circular reference problem. The
subroutine you gave me also refreshed the other cells. Maybe becuase the
worksheet automatically refreshes whenever a cell is updated?



"Mike H" wrote in message
...
Hi,

Excel won't like that because the correct syntax is

=IF(AND(A6="",B6<""),"",NOW())

But you will still have the same problem of NOW() updating every time
the worksheet calculates as soon as those 2 conditions evaluate as TRUE

Mike
"Mark A. Sam" wrote in message
...
This may be awkward for the client and before I do this, I'd like to
ask another question.

I tried modifying the formula using the And operator to test the value
of the cell containing the formula,

=IF(A6="" and B6<"","",NOW())

Excel didn't like it. Am I referencing the cells incorrectly or is
this just an illegal operation?

God Bless,

Mark

"Mike H" wrote in message
...
Hi,

You could do this. Right click your sheet tab, view code and paste
this in. It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client is using this formula to update the date and time in a
particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when
any cell is updated. Does this require a code and if so how to
implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Why does A1 receive a date when you change A1?

What is in A1? Formula or?

The value in B1 is the serial number of NOW()

Format B1 to Date


Gord

On Fri, 20 Mar 2009 16:30:38 -0400, "Mark A. Sam"
wrote:

It is getting there, but not quite. I placed the formula into cell B1, but
it when I update A1, it receives that date (20/3/2009 16:21) and B1
receives an unknown value (39892.68145).


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Did you try the circular reference formula from John's site?

=IF(A1="","",IF(B1="",NOW(),B1))

Of course, after changing the Iterations setting.


Gord

On Fri, 20 Mar 2009 15:54:40 -0400, "Mark A. Sam"
wrote:

Gord.

What he is looking to do is to copy the the formula from one cell to
another. After seeing how it works, I think the coding option may be to
cumbersome for him. It will require modifying the code for each cell he
wants to target, at least that is how it seems to me. I used on cell as
an
example, but he will be addressing many cells. Am I wrong in my thinking?

What I think I need to do is modify the formula something like this:

=IF(A6="" and B6<"","",NOW())

But that doesn't work. It is illegal syntax.

God Bless,

Mark




"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mark

Couple of methods on John McGimpsey's site

One without VBA and one with.

http://www.mcgimpsey.com/excel/timestamp.html


Gord Dibben MS Excel MVP

On Fri, 20 Mar 2009 15:17:41 -0400, "Mark A. Sam"
wrote:

Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client
is using this formula to update the date and time in a particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell
is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Hi,

yes that makes perfect sense. Right click your sheet tab, view code and
paste the code below in. An enter in column A now makes column B populate
with a static data/time

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(, 1).Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub

Mike

"Mark A. Sam" wrote:

Mike,

He is using Column A to enter data and wants the corresponding cell in
Column B updated with the Date/Time:

A1 B1
..53 3/20/2009 14:00
..4 3/20/2009 14:21

Does that make sense?




"Mike H" wrote in message
...
Hi,

It gave a circular reference because you put it in either A6 or B6, you
never got around to telling us which cell you wanted the date/time in. No
matter how long you play with this unless you use the circular reference
method given to you by Gord you won't achieve what you want using a
formula. If you want to use code then tell us the range of cells to look
at and where to put the date & time.

Mike

"Mark A. Sam" wrote in message
...
Mike,

=IF(AND(A6="",B6<""),"",NOW()) gave me circular reference problem. The
subroutine you gave me also refreshed the other cells. Maybe becuase the
worksheet automatically refreshes whenever a cell is updated?



"Mike H" wrote in message
...
Hi,

Excel won't like that because the correct syntax is

=IF(AND(A6="",B6<""),"",NOW())

But you will still have the same problem of NOW() updating every time
the worksheet calculates as soon as those 2 conditions evaluate as TRUE

Mike
"Mark A. Sam" wrote in message
...
This may be awkward for the client and before I do this, I'd like to
ask another question.

I tried modifying the formula using the And operator to test the value
of the cell containing the formula,

=IF(A6="" and B6<"","",NOW())

Excel didn't like it. Am I referencing the cells incorrectly or is
this just an illegal operation?

God Bless,

Mark

"Mike H" wrote in message
...
Hi,

You could do this. Right click your sheet tab, view code and paste
this in. It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client is using this formula to update the date and time in a
particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when
any cell is updated. Does this require a code and if so how to
implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

Thank you Mike. It works great.


"Mike H" wrote in message
...
Hi,

yes that makes perfect sense. Right click your sheet tab, view code and
paste the code below in. An enter in column A now makes column B populate
with a static data/time

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(, 1).Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub

Mike

"Mark A. Sam" wrote:

Mike,

He is using Column A to enter data and wants the corresponding cell in
Column B updated with the Date/Time:

A1 B1
..53 3/20/2009 14:00
..4 3/20/2009 14:21

Does that make sense?




"Mike H" wrote in message
...
Hi,

It gave a circular reference because you put it in either A6 or B6, you
never got around to telling us which cell you wanted the date/time in.
No
matter how long you play with this unless you use the circular
reference
method given to you by Gord you won't achieve what you want using a
formula. If you want to use code then tell us the range of cells to
look
at and where to put the date & time.

Mike

"Mark A. Sam" wrote in message
...
Mike,

=IF(AND(A6="",B6<""),"",NOW()) gave me circular reference problem.
The
subroutine you gave me also refreshed the other cells. Maybe becuase
the
worksheet automatically refreshes whenever a cell is updated?



"Mike H" wrote in message
...
Hi,

Excel won't like that because the correct syntax is

=IF(AND(A6="",B6<""),"",NOW())

But you will still have the same problem of NOW() updating every time
the worksheet calculates as soon as those 2 conditions evaluate as
TRUE

Mike
"Mark A. Sam" wrote in message
...
This may be awkward for the client and before I do this, I'd like to
ask another question.

I tried modifying the formula using the And operator to test the
value
of the cell containing the formula,

=IF(A6="" and B6<"","",NOW())

Excel didn't like it. Am I referencing the cells incorrectly or is
this just an illegal operation?

God Bless,

Mark

"Mike H" wrote in message
...
Hi,

You could do this. Right click your sheet tab, view code and paste
this in. It puts the static date/time in A3 whe A2 is updated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Range("A6").Value = Format(Now, "dd/mm/yyyy hh:mm")
Application.EnableEvents = True
End If
End Sub


Mike
"Mark A. Sam" wrote in message
...
Hello,

I'm not too proficient with Excel so hopefully this can be done.
My
client is using this formula to update the date and time in a
particular cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when
any cell is updated. Does this require a code and if so how to
implement it.

Thank you for any help and God Bless,

Mark A. Sam

Refreshing one cell
 

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Why does A1 receive a date when you change A1?

I don' know


What is in A1? Formula or?

Nothing. I type in a value.


The value in B1 is the serial number of NOW()

Format B1 to Date

Mike H gave me some code that works. I hope that is all the client wants.
I'm his Access developer and don't use Excel much except to write code to
export data from Access to Excel.

Thanks for your help and God Bless,

Mark




Gord

On Fri, 20 Mar 2009 16:30:38 -0400, "Mark A. Sam"
wrote:

It is getting there, but not quite. I placed the formula into cell B1,
but
it when I update A1, it receives that date (20/3/2009 16:21) and B1
receives an unknown value (39892.68145).


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Did you try the circular reference formula from John's site?

=IF(A1="","",IF(B1="",NOW(),B1))

Of course, after changing the Iterations setting.


Gord

On Fri, 20 Mar 2009 15:54:40 -0400, "Mark A. Sam"
wrote:

Gord.

What he is looking to do is to copy the the formula from one cell to
another. After seeing how it works, I think the coding option may be to
cumbersome for him. It will require modifying the code for each cell he
wants to target, at least that is how it seems to me. I used on cell as
an
example, but he will be addressing many cells. Am I wrong in my
thinking?

What I think I need to do is modify the formula something like this:

=IF(A6="" and B6<"","",NOW())

But that doesn't work. It is illegal syntax.

God Bless,

Mark




"Gord Dibben" <gorddibbATshawDOTca wrote in message
m...
Mark

Couple of methods on John McGimpsey's site

One without VBA and one with.

http://www.mcgimpsey.com/excel/timestamp.html


Gord Dibben MS Excel MVP

On Fri, 20 Mar 2009 15:17:41 -0400, "Mark A. Sam"

wrote:

Hello,

I'm not too proficient with Excel so hopefully this can be done. My
client
is using this formula to update the date and time in a particular
cell:

=IF(A2="","",NOW())

He wants it to update only when A2 is updated, but it updates when any
cell
is updated. Does this require a code and if so how to implement it.

Thank you for any help and God Bless,

Mark A. Sam