Expired Employee Certifications

[shifty1]

I have almost 200 employees with various certifications, what I am trying to do is make a simple book to show the dates of certification, and to highlight an expired/soon expired in the following format: orange (90days from expiration, Yellow (45 Days from Expiration) and Red (30 Days from Expiration). The certification period is 2 years, so every 2 years a recert is required.

The second part (to where I am confused and frustrated) is trying to have a list automatically generated on sheet 2, of all personnel requiring re-certification. This would include having the entire row (name, call sign, dates etc) transferred over to sheet 2 so theres an updated list of personnel who need to requalify.

I have attached the book I have been working on. Please let me know if there is anything anyone can do to help me out on this. I appreciate it.

Please help!

Thanks everyone!!!

From, Afghanistan 8/9/2011 18:21 hrs

[Blake 7]

for the second part......

"The second part (to where I am confused and frustrated) is trying to have a list automatically generated on sheet 2, of all personnel requiring re-certification. "

In sheet 2 cell B4

=IF(Sheet1!$H5="No","",VLOOKUP('Sheet1 (2)'!$A5,Sheet1!$A$4:$D$37,COLUMNS('Sheet1 (2)'!$A$1:B$1),0))

copy across and down

[usafmeinweg]

I've attached a worksheet with some of what you want. I am working on pulling the data to a separate sheet. In the meantime, I added a column called Expired. I have also made it so that you can filter the data. If you click the filter button on "Expired", you can filter by selecting all, Yes, No, and within 30 days. Let me know if this will work for you or if you still want a separate sheet to pull the data for you