Track Excel cell history in separate sheets

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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...


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


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


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


So that when you update the date and the description on the "current" sheet...


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.


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
 

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?
 

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?
 

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
 

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.
 

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.
 

pmol

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

Attachments

OBP

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

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).
 

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.
 

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?
 

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.
 

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.
 

Attachments

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top