Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Stamp when info is updated
I have an excel table that is imported from Access. I have a column named
Price and I need a column next to it called Date that will show when that specific price is updated. I have been searching around and most of the things I have come across have not been working becuase everythime I update the entire table the dates change with the table. I need something that will only change when a specific cell changes. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Stamp when info is updated
Hi,
I have handled this before by including an "Update" date column in the MS-Access table, and then capture the date and time each record is updated in MS-Access. Then when you import your records into Excel, the info is already there...perhaps this would work? "NervousFred" wrote: I have an excel table that is imported from Access. I have a column named Price and I need a column next to it called Date that will show when that specific price is updated. I have been searching around and most of the things I have come across have not been working becuase everythime I update the entire table the dates change with the table. I need something that will only change when a specific cell changes. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Stamp when info is updated
Hrmm didn't think of that.
How would I do that in a query in access? My data that I use to import into excel is a query of a larger database. Also would each date remain the same and only update if its corrisdoning cell updated or would all of the dates update each time the query was refreshed. As I said earlier, I just need each date to update ONLY if the price next to it CHANGES. "XP" wrote: Hi, I have handled this before by including an "Update" date column in the MS-Access table, and then capture the date and time each record is updated in MS-Access. Then when you import your records into Excel, the info is already there...perhaps this would work? "NervousFred" wrote: I have an excel table that is imported from Access. I have a column named Price and I need a column next to it called Date that will show when that specific price is updated. I have been searching around and most of the things I have come across have not been working becuase everythime I update the entire table the dates change with the table. I need something that will only change when a specific cell changes. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Stamp when info is updated
Assuming your Price column is Column C and your Date column is Column D,
then give this worksheet event code a try. To install it, right-click the tab for the worksheet containing your table and copy/paste the following code into the code window that appeared... '********** START OF CODE ********** Dim CellValue As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then If Target.Value < CellValue Then Cells(Target.Row, "D").Value = Now End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 3 Then CellValue = Target.Value End Sub '********** END OF CODE ********** Now, whenever you change a value in Column C, the current date/time will be entered in the same row in Column D. There are two logical statements for Column C (they are Target.Column=3) that you will need to change in case I guessed wrong as to which column has your Prices in it. Rick "NervousFred" wrote in message ... I have an excel table that is imported from Access. I have a column named Price and I need a column next to it called Date that will show when that specific price is updated. I have been searching around and most of the things I have come across have not been working becuase everythime I update the entire table the dates change with the table. I need something that will only change when a specific cell changes. Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Stamp when info is updated
In looking at the other sub-thread to your posting, I think the answer I
gave you is not what you are looking for. The code I posted assumes you loaded your data from wherever and will insert a date in response to you changing a single cell by typing in a new value. If you are reloading new information over top of the old data, the code I posted won't work for you. Rick "Rick Rothstein (MVP - VB)" wrote in message ... Assuming your Price column is Column C and your Date column is Column D, then give this worksheet event code a try. To install it, right-click the tab for the worksheet containing your table and copy/paste the following code into the code window that appeared... '********** START OF CODE ********** Dim CellValue As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then If Target.Value < CellValue Then Cells(Target.Row, "D").Value = Now End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 3 Then CellValue = Target.Value End Sub '********** END OF CODE ********** Now, whenever you change a value in Column C, the current date/time will be entered in the same row in Column D. There are two logical statements for Column C (they are Target.Column=3) that you will need to change in case I guessed wrong as to which column has your Prices in it. Rick "NervousFred" wrote in message ... I have an excel table that is imported from Access. I have a column named Price and I need a column next to it called Date that will show when that specific price is updated. I have been searching around and most of the things I have come across have not been working becuase everythime I update the entire table the dates change with the table. I need something that will only change when a specific cell changes. Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Stamp when info is updated
hi
sounds like the price needs to be updated in access and the date of update entered into access THEN downloaded to excel. Regards FSt1 "NervousFred" wrote: I have an excel table that is imported from Access. I have a column named Price and I need a column next to it called Date that will show when that specific price is updated. I have been searching around and most of the things I have come across have not been working becuase everythime I update the entire table the dates change with the table. I need something that will only change when a specific cell changes. Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Stamp when info is updated
Rick,
Yes, I have the table set to refresh everytime the spreadsheet is opened. This will affect the dates in your code correct? Anyone know how to do the time stamps in Access? If not I can just always post in the access forums. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
Updated times stamp of last change | Excel Discussion (Misc queries) | |||
Create a formula to calculate when info. was last updated | Excel Discussion (Misc queries) | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
Date stamp spreadsheet in excel to remind me of completion date | Excel Worksheet Functions |