1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Track Excel cell history in separate sheets

Discussion in 'Business Applications' started by pmol, Jan 5, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. pmol

    pmol Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    10
    Hi,

    I'm developing a way to keep a history goals and was wondering if it's possible to track changes of specific cells into separate sheets. I'm operating on Windows 7, working in Microsoft Office 2007.

    For Example:
    If the following may be the main view, with the current information and each "activity" with its own sheet...
    [​IMG]

    And the date and description of each "activity" from the "current" sheet is referenced in the "activity" sheet...
    [​IMG]

    I'm wondering if the following is possible (the rest of the slides are theoretical):
    If you change the date in the "current" sheet...
    [​IMG]

    It will automatically generate a new row of information in the "activity" sheet, without deleting the previous entry...
    [​IMG]

    So that when you update the date and the description on the "current" sheet...
    [​IMG]

    The information is automatically logged historically on the appropriate "activity" sheet, so any activity's history can be viewed at any time from its "activity" sheet, by ONLY changing information on the "current" sheet.
    [​IMG]

    It's sort of like organized twitter, I guess. Not sure if this is possible (in any similar way!) but would really appreciate some help! Thanks!

    -Hannah
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Hannah, welcome to the Forum.
    Yes you can do what you outline, but the control will need to be a bit better than shown.
    i.e. the sheet names should really be exactly the same as the "Acivity" for Excel to know what sheet to history sheet to update.
    Have you got Access?
     
  3. pmol

    pmol Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    10
    Excellent, I'm up for some changes, that was a total first draft.
    Yes, I have Access (2007), but I haven't played around with it much. What do I need to do?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Which route do you want to use?
    Access is far superior contolling how the data gets entered, but requires a bit more work setting it up.
    The Excel route requires some VBA code (Macro) that will put the data in to the other sheets.
    It is not necessary to do so with Access because all the old data goes in one table and is then Collated by Queries.
    I would recommend Access, but then it is my favourite and I do most of the work for you, but it is up to you entirely
     
  5. pmol

    pmol Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    10
    My only hesitation is that I have virtually no experience with Access, whereas I feel adequate in my experience and understanding coding in Excel (though admittedly not much with VBAs). But if you feel you're better able to help me by using Access and it will work like how I mentioned, I'd be willing to try it out.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you post an excel sheet with some dummy data, like the one that you have shown, but in Excel 2000 format?
    I will look at both options and advise from there.
     
  7. pmol

    pmol Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    10
    here's the doc saved in 2003-2007, I hope that's helpful.
    Thank you.
     

    Attached Files:

  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    That's great.
    Can I ask what you intended using the "Next" column for?
     
  9. pmol

    pmol Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    10
    that's a little less important if you can work it out, but in the other document I created I was rating things based on how well it's going in planning for doing them next (so a 1 indicates that it's well in the works of happening, whereas a 5 would mean it doesn't seem like it's going to happen out any time soon).
     
  10. pmol

    pmol Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    10
    I was thinking it might be cool to track each activity's progress in a graph, but that would be way less necessary than the microblog.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, I have some "operational" type questions.
    What happens if someone changes the date & forgets to update the Description?
    What happens if someone changes the Description & forgets to update the Date?
    If you are going to have the date change trigger the VBA code it will have to come after the Description.
    Is "Over-writing" the cells the best data entry method?
     
  12. pmol

    pmol Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    10
    Ideally, if either description or date forgets to be changed, then either are left blank.

    For example, something may end up looking like:
    01 activity
    04 activity
    06 _____
    07 activity
    09 activity
    __ activity
    17 activity

    But there should never be an instance of "_ ____"

    I guess it doesn't matter if the date or the description are listed first, though the date first does look nicer.

    Over-writing the cells is the only method I can see that would mean I ideally only ever enter data on one page, unless the main page was constantly blank with a 'submit' button or something.

    It would be preferable that you would be able to edit the information in the "activity" page after it's been submitted without compromising the formatting on the data entry page.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This is an Access version for an example, the top section allows you to select an Activity to see it's current state in the list below, to see it's History click the All data Tab.
    New activity Info go in the new record (last line) in the All data Tab, it will automatically update the main form and other tab.
    The all data records are shown Last Record first, so that you can see the latest data at the top.
     

    Attached Files:

  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This is the Excel version, the VBA code is activated when you change the date.
     

    Attached Files:

  15. pmol

    pmol Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    10
    These are great!

    The access version feels more tedious to me (possibly my lack of experience with it), so I'm leaning toward the excel version. I like being able to type in the activity, or use an already existing column instead of using a drop down menu. For me, it's strange to have typing fields that have text in them, but not be allowed to edit them, which seems to be how Access is set up. There are possible advantages that I'm totally ignorant to.

    How do you add activities? (I guess just to the Excel version, so you can focus).

    & How did you do it? I'm really interested, but I don't see any codes or anything in the boxes... I know you used Macros, can I view them or see how they work so I can fiddle around with it for tracking the "next" column, or add activities, whatever.

    Thanks again so much. Your help is very much appreciated.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/972783

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice