Help on updating info based on date

[rero360]

Good Evening everyone, I am hoping I could get some help. I want to create a workbook that updates every time it is opened. What I'm trying to do is track everyone in my company, when they enlisted or commissioned, so that when the workbook is opened, it updates and on sheet 1 says who has earned what awards. Some military awards are earned after various periods of good service, one year for one, three years for others, ten years for others still. What I'd like it to do is when it opens sheet one becomes populated with the names of everyone who earned an award that month, and then shows what awards and finally, has a list of total longevity awards.

For example, workbook is opened (in Sept 2015), on sheet 1 it lists:

-------------CADAR--- CASM--- CAGCM--- ARCAM --- AFRM-------- CADAR --- CASM ---CAGCM ---ARCAM--- AFRM
E4 Snuffy, Joe - X----------------- X-------- X ----------------------6--------- 0--------- 2-------- 2-------- 0
enlisted Sept 5 2009(this would stay on sheet 3)
O2 Dix, Richard - X-------------------------------------------------- 4----------0--------- 1--------- 1--------0
commissioned Sept 29 2011

Is this possible? And if so, what would be starting point to get it to calculate everything off of the computer's date? I'm currently taking a VBA course but its geared towards engineering and the professor said that we wouldn't be covering anything like this. My admin people are horribly understaffed and I want to create something that will speed up at least one task for them. Finally, how intensive of a code is this going to be?

Thank you everyone.

[SamT]

Start with a Data sheet, with all Data Point names (Fields) in the Header Row. [LstName, FstName, Rank, DOE, DOR, Award Title, Award2 Title, Etc.

Awards and Commendations are also earned in the field: Add a Special Awards sheet with LstName and FstName Headers. The remaining Values for each Record would be positionally identified.

Bailey

Beetle

Bronze Star

7-Dec-42

Letter Of Commendation

3-Sep-15

(I recommend that you use that particular date style when entering dates manually. Format the cells to display your preferred style)

Add a hidden VBA_Variables sheet to save particular values, such as "LatestMonthChecked" = 1-Aug-15

Finally, design your Monthly Awards Display sheet

In your Main Procedure, called by the Workbook_Open sub,

(Call Sub) CheckDOE

If Month(Now) > Month(LatestMonthChecked) Then 
(Call Sub) UpdateMonthlyAwardsDisplay

Sub CheckDOE()
For Each Cel in DataSheet.Range(DOE)
Select Case Now - Cel.Value
Case 1 (Year): UpdateDataSheet(LstName, FstName, OneYearAward)
Case 3:  Etc
End Select
UpdateMonthlyAwardsDisplay
End Sub

Inre the Special Awards sheet, I would use its Worksheet_Activate sub to call a UserForm to perform all editing on the sheet. The UserForm would also be charged with updating the Monthly Awards Display sheet.

Note that you can paste Ranges from Excel directly into the message editor, and if you use the "Go Advanced" option, You can format the Tables created and add Attachments to your message.

[rero360]

Thank you, it'll be a while but I'll be sure to update this as I make progress in my project.