Excel 2007 : Track annual training

**duckhunthero** · 12-20-2010, 04:51 PM

I need some help here. I have read and searched and read some more but cant seem to get this to work right. I figured out the conditional formatting from some threads on the page. Thanks. Still need the sheet to sheet part though.

I have Rank LName and FName and MI in the first 4 column's and each type of annual training after that. Rifle Range, Physical Fitness Test, Combat Fitness test, Gas CHmaber Etc Etc.

I would like to make another sheet that populates with all the names that are out of date for Gas Chamber.

I know this is asking a lot from you guys here but I have been getting info from here for quite a while. I use the tip section quite often and it was very helpful when tracking maintenance of 5 different buildings. However on this one I just cant get it to work.

Thank you in advance.

**alansidman** · 12-21-2010, 02:44 PM

I have assumed for this purpose that they are overdue if a period of one year has elapsed since the last test.

In cell A3 on the next sheet, type
=IF('S4'!L3+365>TODAY(),"",'S4'!B3&" "& 'S4'!D3&" "&'S4'!C3)
and copy it down. See your spreadsheet attached for example.

Alan

**duckhunthero** · 12-21-2010, 09:29 PM

Originally Posted by alansidman

I have assumed for this purpose that they are overdue if a period of one year has elapsed since the last test.

In cell A3 on the next sheet, type
=IF('S4'!L3+365>TODAY(),"",'S4'!B3&" "& 'S4'!D3&" "&'S4'!C3)
and copy it down. See your spreadsheet attached for example.

Alan

First let me say thank you so much for your time. It is greatly appreciated. Just so you know Im trying to make my job of tracking annual training on 110 Marines easier. It consumes a lot of time that I just dont think it should.
That said, how could I get it to pull the date column over too. Can you also explain the formula little too. I have tried changing it up some with no success. it isnt always 365 past due. If they did a gas chamber between 1 Jan 2010 and 31 Dec 2010 their considered up to date. The goal is to make a tab for every training requirement. name the tab gas chamber. Click that tab and it shows me all of them that are out of date with out having to use advanced filter or run a macro.
I got it to work somewhat with advanced filter and Im trying some macros stuff too.

Once again thank you!

**alansidman** · 12-21-2010, 10:45 PM

The if formula I presented says that if the last date plus 1 year is less than today, then they are due for training, otherwise, do not list their name.

If we take the scenario, that last training was prior to 2010, then they would need training.

The formula using the same cells would read:

=if('S4'!L3<1/1/10,'S4'!B3&" "& 'S4'!D3&" "&'S4'!C3,"")

Using the same syntax for an if statement, you could pass a true value for the date to an adjacent cell.

Look at this for understanding how if statements work.
http://www.techonthenet.com/excel/formulas/if.php
and this if you have more than one if to fulfill
http://www.techonthenet.com/excel/fo.../if_nested.php

Alan

**MarvinP** · 12-21-2010, 11:28 PM

Hi duckhunterhero,

If you structure your data collection a little differently, Tables, Auto Filters and even Pivot Tables can be used to get the results you may be looking for.

I've given you an example on the attached, below your original data. Using Tables with filters allows Excel to do a lot more work than with your original format.

**duckhunthero** · 12-22-2010, 01:36 AM

Marvin and Alan Thank you both very much. I have added to your reputation.

Alan, I will read up on those learn as much as I can.
Marvin, Im sure it is it will take me some time to figure out just how to use all table and filters in that spread sheet.
Also Im heading down to the education center tomorrow to sign up for some Microsoft office classes.