Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Is there a formula that can be inserted into a cell that will put the current
date in another cell whenever someone edits the contents within a specified range of cells? For example, A5:M5 contain data, and N5 contains a date. Column "N" is titled "Last Updated". Therefore, whenever someone changes the contents contained in A5:M5, then N5 would automatically show the current date. I'm comfortable in VBA if that's the ultimate answer, but was hoping for an if/then formula. Would assume the formula could be built in, say O5. Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
A formula can only put the result in its (own) cell so you will need to use
VBA (worksheet event). "Mapasque" wrote: Is there a formula that can be inserted into a cell that will put the current date in another cell whenever someone edits the contents within a specified range of cells? For example, A5:M5 contain data, and N5 contains a date. Column "N" is titled "Last Updated". Therefore, whenever someone changes the contents contained in A5:M5, then N5 would automatically show the current date. I'm comfortable in VBA if that's the ultimate answer, but was hoping for an if/then formula. Would assume the formula could be built in, say O5. Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Thank you. My thought was something like (in O5): If A5:M5 are "CHANGED???",
then N5=datenow(). It could also reside in N5, but didn't think that was possible. Perhaps there is no formula programming equivalent to capture the the "Changed" concept. Since I have about 150 rows of projects and each row would need this formula, I'm not sure how I would write this in VBA. Thanks for taking time to respond. "Toppers" wrote: A formula can only put the result in its (own) cell so you will need to use VBA (worksheet event). "Mapasque" wrote: Is there a formula that can be inserted into a cell that will put the current date in another cell whenever someone edits the contents within a specified range of cells? For example, A5:M5 contain data, and N5 contains a date. Column "N" is titled "Last Updated". Therefore, whenever someone changes the contents contained in A5:M5, then N5 would automatically show the current date. I'm comfortable in VBA if that's the ultimate answer, but was hoping for an if/then formula. Would assume the formula could be built in, say O5. Thank you in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo endit With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:M150"), .Cells) Is Nothing Then Application.EnableEvents = False If Not IsEmpty(.Value) Then Cells(.Row, "N").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End If End With endit: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. A change to any cell in A1:M150 will place the date/time in N Gord Dibben MS Excel MVP On Wed, 20 Jun 2007 14:54:28 -0700, Mapasque wrote: Thank you. My thought was something like (in O5): If A5:M5 are "CHANGED???", then N5=datenow(). It could also reside in N5, but didn't think that was possible. Perhaps there is no formula programming equivalent to capture the the "Changed" concept. Since I have about 150 rows of projects and each row would need this formula, I'm not sure how I would write this in VBA. Thanks for taking time to respond. "Toppers" wrote: A formula can only put the result in its (own) cell so you will need to use VBA (worksheet event). "Mapasque" wrote: Is there a formula that can be inserted into a cell that will put the current date in another cell whenever someone edits the contents within a specified range of cells? For example, A5:M5 contain data, and N5 contains a date. Column "N" is titled "Last Updated". Therefore, whenever someone changes the contents contained in A5:M5, then N5 would automatically show the current date. I'm comfortable in VBA if that's the ultimate answer, but was hoping for an if/then formula. Would assume the formula could be built in, say O5. Thank you in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Gord,
What about if instead of range ("A1:M150") I just want the change in cell B10 to trigger the date to be put in cell I3 (instead of row N)? PS: As you will probably notice from my question, I am completely new to VB! -- Merci! Bonzai "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo endit With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:M150"), .Cells) Is Nothing Then Application.EnableEvents = False If Not IsEmpty(.Value) Then Cells(.Row, "N").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End If End With endit: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. A change to any cell in A1:M150 will place the date/time in N Gord Dibben MS Excel MVP On Wed, 20 Jun 2007 14:54:28 -0700, Mapasque wrote: Thank you. My thought was something like (in O5): If A5:M5 are "CHANGED???", then N5=datenow(). It could also reside in N5, but didn't think that was possible. Perhaps there is no formula programming equivalent to capture the the "Changed" concept. Since I have about 150 rows of projects and each row would need this formula, I'm not sure how I would write this in VBA. Thanks for taking time to respond. "Toppers" wrote: A formula can only put the result in its (own) cell so you will need to use VBA (worksheet event). "Mapasque" wrote: Is there a formula that can be inserted into a cell that will put the current date in another cell whenever someone edits the contents within a specified range of cells? For example, A5:M5 contain data, and N5 contains a date. Column "N" is titled "Last Updated". Therefore, whenever someone changes the contents contained in A5:M5, then N5 would automatically show the current date. I'm comfortable in VBA if that's the ultimate answer, but was hoping for an if/then formula. Would assume the formula could be built in, say O5. Thank you in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo endit With Target If .Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("B10")) Is Nothing Then Application.EnableEvents = False Range("I3").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With endit: Application.EnableEvents = True End Sub Gord On Wed, 20 Jun 2007 18:30:00 -0700, Bonzai wrote: Gord, What about if instead of range ("A1:M150") I just want the change in cell B10 to trigger the date to be put in cell I3 (instead of row N)? PS: As you will probably notice from my question, I am completely new to VB! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Works perfectly! Thank you very much.
"Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo endit With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:M150"), .Cells) Is Nothing Then Application.EnableEvents = False If Not IsEmpty(.Value) Then Cells(.Row, "N").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End If End With endit: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. A change to any cell in A1:M150 will place the date/time in N Gord Dibben MS Excel MVP On Wed, 20 Jun 2007 14:54:28 -0700, Mapasque wrote: Thank you. My thought was something like (in O5): If A5:M5 are "CHANGED???", then N5=datenow(). It could also reside in N5, but didn't think that was possible. Perhaps there is no formula programming equivalent to capture the the "Changed" concept. Since I have about 150 rows of projects and each row would need this formula, I'm not sure how I would write this in VBA. Thanks for taking time to respond. "Toppers" wrote: A formula can only put the result in its (own) cell so you will need to use VBA (worksheet event). "Mapasque" wrote: Is there a formula that can be inserted into a cell that will put the current date in another cell whenever someone edits the contents within a specified range of cells? For example, A5:M5 contain data, and N5 contains a date. Column "N" is titled "Last Updated". Therefore, whenever someone changes the contents contained in A5:M5, then N5 would automatically show the current date. I'm comfortable in VBA if that's the ultimate answer, but was hoping for an if/then formula. Would assume the formula could be built in, say O5. Thank you in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Thank you Gord for helping! Any suggestions where a newbie like me should
start to learn VBA basics? Thanks again. -- Merci! Bonzai "Gord Dibben" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo endit With Target If .Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("B10")) Is Nothing Then Application.EnableEvents = False Range("I3").Value = Format(Now, "dd mmm yyyy hh:mm:ss") End If End With endit: Application.EnableEvents = True End Sub Gord On Wed, 20 Jun 2007 18:30:00 -0700, Bonzai wrote: Gord, What about if instead of range ("A1:M150") I just want the change in cell B10 to trigger the date to be put in cell I3 (instead of row N)? PS: As you will probably notice from my question, I am completely new to VB! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Try David McRitchie's "getting started with vba and macros" site.
http://www.mvps.org/dmcritchie/excel/getstarted.htm Note also the links to other sites at page bottom. Gord On Thu, 21 Jun 2007 10:25:01 -0700, Bonzai wrote: Thank you Gord for helping! Any suggestions where a newbie like me should start to learn VBA basics? Thanks again. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to change date when other cells are modifed
Thank you very much for the advice, appreciate it.
-- Merci! Bonzai "Gord Dibben" wrote: Try David McRitchie's "getting started with vba and macros" site. http://www.mvps.org/dmcritchie/excel/getstarted.htm Note also the links to other sites at page bottom. Gord On Thu, 21 Jun 2007 10:25:01 -0700, Bonzai wrote: Thank you Gord for helping! Any suggestions where a newbie like me should start to learn VBA basics? Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i change the date formula | Setting up and Configuration of Excel | |||
I need a formula that change all date by putting in one date | Excel Worksheet Functions | |||
Can I program Excel cells to change colour at a set date? | Excel Discussion (Misc queries) | |||
how do I format cells to change date and time to just date | Excel Discussion (Misc queries) | |||
How go I get cells to change color if a date is greater than 365 . | Setting up and Configuration of Excel |