+ Reply to Thread
Results 1 to 8 of 8

Time and date stamp of dynamic cell plus look up for last entry of the month.

  1. #1
    Registered User
    Join Date
    12-14-2018
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    5

    Time and date stamp of dynamic cell plus look up for last entry of the month.

    I have a worksheet that has a dynamic cell (A1) with a constantly changing value. I want to record a history of this value and the time and date that it has changed. I have found some VBA solutions online that can do a time/date stamp or a list history of the value but not a VBA solution that does both. So ideally I would have:

    A1 - Dynamic value
    B1 - First historic value of A1, C1 - Date/time stamp
    B2 - Second historic value of A1, C2 - date/time stamp
    and so on.

    Once I have this list I would then wish to be able to lookup the list so that I could get the historic value of A1 at the end of each month and record this so that it can feed in to a performance dashboard. I did have an array formula solution that was initially working but this formula was made using indirect and then became volatile.

    I hope someone can help. Thank you.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    2007 Enterprise
    Posts
    4,232

    Re: Time and date stamp of dynamic cell plus look up for last entry of the month.

    .
    Paste into the Sheet Module for Sheet 1 :

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,670

    Re: Time and date stamp of dynamic cell plus look up for last entry of the month.

    Try something like this:
    Please Login or Register  to view this content.
    Make sure you have a sheet called Log and "seed" it with a header row.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    Registered User
    Join Date
    12-14-2018
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    5

    Re: Time and date stamp of dynamic cell plus look up for last entry of the month.

    Logit, this works thanks very much but for some reason I got multiple entries (277 rows) after the cell changed one time. Is there something I need to change in my excel options? Or a tweak to the VBA?

  5. #5
    Registered User
    Join Date
    12-14-2018
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    5

    Re: Time and date stamp of dynamic cell plus look up for last entry of the month.

    dflak, Thanks for your response. It hasn't worked when I've put it in to my spreadsheet. Is there something I am missing?

  6. #6
    Registered User
    Join Date
    12-14-2018
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    5

    Re: Time and date stamp of dynamic cell plus look up for last entry of the month.

    Actually it works if I manually change the cell value but not when it has a formula in cell A1. Any ideas?

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    2007 Enterprise
    Posts
    4,232

    Re: Time and date stamp of dynamic cell plus look up for last entry of the month.

    Quote Originally Posted by Samboco View Post
    Logit, this works thanks very much but for some reason I got multiple entries (277 rows) after the cell changed one time. Is there something I need to change in my excel options? Or a tweak to the VBA?
    Not that I am aware of. The workbook functions as desired here.

    Did you download the attached workbook ? If you did, the attached workbook is creating 277 additional lines of time stamps ?

    Sorry ... I don't have an answer.

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,670

    Re: Time and date stamp of dynamic cell plus look up for last entry of the month.

    Quote Originally Posted by Samboco View Post
    dflak, Thanks for your response. It hasn't worked when I've put it in to my spreadsheet. Is there something I am missing?
    It needs to be put in the module for the sheet where you want it to work; not in a regular module.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1