help with adding new names and classes to my spreadsheet.

Hello,

I have attached a spreadsheet that I'm hoping to get help with. This file was handed down to me, and I'm no excel guru. What I hope to do is learn how to manage this thing on my own someday. I am also using Microsoft Excel 2010 on a Windows 7 machine.

This spreadsheet is a tool that I use to track training that is due every 12 months, and every 24 months. The cell colors are based on the date in D84:

green = training was completed within 12/24 months
yellow = training is due in less than 30 days
red = training is overdue

The problems:
1) Column B has the list of names. 'Mr. L' needs to be inserted between Mr. K and Mr. M. I've tried to cut/copy and insert the row, but the colors in that row end up getting screwed up. Some cells aren't the appropriate color, and other cells in that row change color when a different cell (in the same row) is populated.

1.1) I'm hoping to add at least 5-10 names and they won't necessarily be added at the end of the list (I do it in alphabetical order for work).

2) I need to add a class in between column I and J called 'know your MLS teams'. I've also tried to cut/copy and insert cells, and the colors in the columns get screwed up the same way.

2.1) I'm also hoping to add at least 5-10 more classes in the near future.



NOTES:
A) the rows between 27 and 75 are hidden.
B) there is more information after row 167 that has something to do with the chart. When I make changes in the top chart it seems to affect the information down there.



According to the guy in the office that originally helped me the 'insert copied cells (rows/columns) screws up the formatting of the table. I have no idea what that means, and I've tried looking it up on my own. I'm hoping someone on the forum can help me out.

Thank you in advance.

Wow!

So far it looks like you fixed the problem I was having with adding rows and columns. May I ask what you did? Were the previous formulas just bad? It will help me with maintaining this spreadsheet and any future changes.

Thank you!

To view the 8 existing rules:
Click on the Home Tab, Conditional Formatting, Manage Rules
Show Formatting Rules for This Worksheet.
Select a rule and click on Edit to view the rule makeup.

You will notice that you have 3 rules for the first year and 3 rules for the second year 1 rule for blanks and 1 rule for non blank cells.

Try to follow the examples to accommodate the new data that you want to add. The Applies To fields will have to reflect the addition of the columns.