training tracker

Let me introduce you to the world of tables. This article will help: http://www.utteraccess.com/wiki/Tables_in_Excel.

I converted your data into an Excel Table. Some of the advantages of tables is that they know how big they are so that pivot tables, charts and formulas automatically adjust to accommodate how much data the table has. Also table columns can be addressed by name which makes reading formulas a lot easier. For example =COUNTIF(Table1[Status], "exp soon") looks at the column Status and only takes into consideration the rows in the table so you do not have to look at all million plus rows. This makes a difference when you have a lot of formulas. Also when you add a new row, or delete a row, the formula is still good.

Another good thing about tables is they "copy down." When you add #10 (in cell A20), it will become become part of the table and the formula and the conditional formatting for status will copy down automatically.''

Finally, for this exercise at least, is you can attach slicers to the table. To insert a slicer click in the table, go to the Insert ribbon and select slicer - it is very intuitive from there.

Slicers are filters on the table data, so this gives the dashboard-like appearance. They are also self-cascading meaning the value of one slicer determines what can be selected by the other slicers. So if you select NURSE in Designation, you can only select Names for people who are nurses.

The table will be filtered accordingly. If you click on the funnel icon on the upper right of the slicer, the filter is removed.

I'll make one more recommendation: Data Validation. You may want to use a list type validation for designation and a date type validation for Date Completed and Renewal Date. Here is more information on data validation: http://www.utteraccess.com/wiki/Data_Validation.

P.S. A hint on making a list type validation. Create a table Called "Table_Designation" with a single column called Designation. In this table add the designations.

Now for the source of the validation use =INDIRECT("Table_Designation[Designation]") - This list is dynamic, as you add or delete designations from the list the drop-down will change to reflect the current status of the list. You can even sort the list if you wish.

Just one more use for tables!