Training spreadsheet questions

**Maisiedays** · 05-12-2010, 06:45 PM

Hello,
I am trying to analyze data from an access training database using excel. However I am not having much luck.

In access I have a list of employees, a list of training offered, the date when they took each training and the expiry date for the training.

What I need to do is produce a spreadsheet every month showing who needs to update any training before it expires. I also need to show who has what training so we can decide if they need additional training. For example, If we have training for A, B, C and D I need to see if John Doe has been trained in A through D or if he is missing B, etc. I know this is a tall order because I have been trying to figure it out for hours now, and it is driving me crazy. Can I do this with Excel or am I barking up the wrong tree?

Thanks!
Kath

**JBeaucaire** · 05-12-2010, 06:48 PM

Yes, you can do this in Excel. But first you'll have to export some data TO Excel so you have something to work with and show us so we can assist you in creating a method.

Once you have your data exported to an Excel workbook, click GO ADVANCED and use the paperclip icon to post up your workbook.

**Maisiedays** · 05-13-2010, 08:00 AM

Thank you so much for your offer of help.
I have attached an excel worksheet.
Thanks!
Kath

**JBeaucaire** · 05-13-2010, 11:04 AM

That's a huge data set...and a lot more than 4 training categories. Using formulas, this is a hefty process.

I added a key column to your export sheet, you can see that formula in F2 on that sheet.

On the new sheet, I filtered and listed all the contractors in column A and the training categories across the top in row 1.

Then the formula in B2 does a lookup against the key added on sheet1. That formula is copied to the whole table.

Results:
Date - the date this person's training in the noted category expires
Blank - this person has training but no expiration date is listed
#N/A - not applicable...this person has not received training in this category

**Maisiedays** · 05-14-2010, 05:40 AM

That is beautiful! Thank you so much for the help. Sorry that was such a huge database. I thought that was what you wanted to see. I was making that much more difficult than it had to be.
Thanks again!
Kath