Tech Support Guy banner
Status
Not open for further replies.
1 - 19 of 19 Posts

·
Registered
Joined
·
10 Posts
Discussion Starter · #1 ·
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
 

·
Registered
Joined
·
19,896 Posts
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?
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #3 ·
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?
 

·
Registered
Joined
·
19,896 Posts
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
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #5 ·
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.
 

·
Registered
Joined
·
19,896 Posts
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.
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #9 ·
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).
 

·
Registered
Joined
·
19,896 Posts
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?
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #12 ·
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.
 

·
Registered
Joined
·
19,896 Posts
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

·
Registered
Joined
·
10 Posts
Discussion Starter · #15 ·
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.
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #16 ·
Ah, as long as the new activity and the new sheet are titled the same, and the date column is still a "custom" it will automatically make the new activity work like the rest of them. Very cool.
 

·
Registered
Joined
·
19,896 Posts
Adding Activities to Excel is one of the issues, with Access you just add it to the Activitiies table using a form.
with Excel you have to add a new Sheet, complete with headings and formatting.
It is possible to get the VBA code to rename some sheets that you set up before hand.
To see the VBA press Alt + f11.
 

·
Registered
Joined
·
10 Posts
Discussion Starter · #18 ·
Do you have any recommended references for how to use the code in Excel, or any tips to how to apply the code to something if I wanted to create something new? I'm interested in how it works/ how you got it set up? How do you go about doing this to a blank Excel sheet or something?

Thanks so much for your hep, this is really cool!!
 

·
Registered
Joined
·
19,896 Posts
I would suggest that you buy a book on Excel VBA code if you interested in learning it. They come with examples on CDs and are great fro references as well.
You can also learn a great deal by Recording a Macro of the actions that you take, the only thing with that is you do get some unnecessary codeas well.
If you want to pick up lots of VBA code, search this Forum using the advanced search for Excel posts by
Aj_old
bomb #21
etaf
Jimmy the Hand
Rollin_Again
Zack Barresse
Ziggy1
All those guys are good and if they are not around I fill in a bit, Zack Barresse is in a class of his own being a Microsoft MVP, but his VBA code be a bit heavy for learning as a begginer.
You should also search the VBAX Excel forum as well as that is even more VBA orientated than this Forum.
 
1 - 19 of 19 Posts
Status
Not open for further replies.
Top