 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / July 2008Date 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! 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? > 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 [quoted text clipped - 7 lines] > > Thanks! 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. > Hi, > [quoted text clipped - 14 lines] > > > > Thanks! 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 >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 [quoted text clipped - 8 lines] > > Thanks! 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 > 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 [quoted text clipped - 34 lines] >> >> Thanks! 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. 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 > 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 [quoted text clipped - 7 lines] > > Thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.