Keeping track of employee training

[IcarusFB]

Hi all! I am trying to set up an Excel spreadsheet that will list al
of the employees in our department, along with all of the training eac
employee has. I would like to set it up so that I can keep track o
when an employee's training certificate is about to expire (probabl
with colour codes: yellow when a training certificate is due to expir
in 3 months and red when a training certificate is due to expire in
month).

Yes, I know there are lots of posts on this topic but I don't have an
formal excel training and I can't seem to find any templates to eve
set up what I am trying to do. I would be very grateful if someon
could email me (or catch me on MSN) and send me a copy of any template
they may be using to keep track of the same things I am trying to do.

I did manage to set something up but I am totally confused as to how t
get the formulas set up. If someone is willing to put in the time,
could send them I have and then maybe they could set things up for me
I don't need to send our entire staff list, I would only send a fe
names and then I could figure out the rest myself by changing th
formula to match the different cells.

Thanks for your time and consideration!

email/msn: (e-mail address removed)

 

[pinmaster]

Attach your worksheet to one of the post and people will be glad to tak
a look at it.

Note. you may need to zip your file in order to upload it.


Regards
J

 

[IcarusFB]

Here is what I have so far. It is very simple and the colours are jus
for effect but it will give you an idea of what I am trying to set up.
Each training categoy (eg. CPR, First Aid, PPCT) will have its ow
expiration date. For simplicity purposes, I just made it all 1 year

+-------------------------------------------------------------------
|Filename: untitled.zip
|Download: http://www.excelforum.com/attachment.php?postid=4152
+-------------------------------------------------------------------

 

[pinmaster]

Made a small test sheet, hope it's what your looking for.

Regards
J

+-------------------------------------------------------------------
|Filename: test.zip
|Download: http://www.excelforum.com/attachment.php?postid=4154
+-------------------------------------------------------------------

 

[IcarusFB]

Thanks m8! That is awesome! I wish I could take credit for it when I
go back to work next week. lol. I really appreciate the help. This
new position I have at work is going to keep me busy so you'll probably
see some more questions from me in the future.

Thanks again!

 

[IcarusFB]

I ran into a small problem. I have sheet 1 with staff listed
alphabetically and sheet 2 with staff listed by shift platoon (4 groups
of 7 people each). How do I arrange it so that changes I make on sheet
1 will also appear on sheet 2.

I know there is a way to do this because I found a partial answer on
google, but the problem is that what is in cell C4 in sheet 1 is not
the same as what is in cell C4 in sheet 2 and so on and so on. Both
sheets have the same info but it is all arranged differently.

Is it possible to solve my problem without having to enter the data
twice? (once on sheet 1 and then again on sheet 2).

Thanks!

 

[pinmaster]

If your setup is the same as what you have on your "Attachment" then you
could use a VLOOKUP formula to return the values in the same row as
"Complete":

=VLOOKUP($A$1,Sheet1!$A$1:$G$1000,COLUMN(C:C),0)

copied across

and for the values on same row as "Expires":

=OFFSET(INDEX(Sheet1!$A$1:$A$1000,MATCH($A1,$A$1:$A$1000,0),0),1,COLUMN(B:B))

also copied across

HTH
JG