Excel/Training Matrix

Advertisement


Question
Hi,

I have created a training matrix and set up conditional formatting to alert when a refresh is due.

What i am struggling with is the following,,....

I want to create a summary sheet thta pulls in who needs which training in a month.
There are 20 or so different courses going horizontaly, 220 employees verticaly and the expiry dates filling the matrix.

I have tried to set up another tab for monthly summaries but cannot work out how to pull a list of people due on courses for the month.

Can you help at all with this?

Regards,

Andy

Answer
Hi Andy,

To confirm my understanding, if you open the sheet today, you should be able to see which trainings are overdue for which employees. You are using conditional formatting to highlight the overdue ones within the same table. I'm assuming you are using the =today() function or storing current date manually.

In order to get a summary, you need not create a totally new tab, you can reuse the first sheet itself by following the below steps:

1. Assuming you store your current date in cell A1.
2. Assuming your courses are stored in columns B to U and names are stored in rows A3 to A223. (A1 contains today's date and cells A2:U2 contain column headings)
3. Create a new column heading in Column "V" - Number of courses overdue
4. Enter this formula in cell V3: =COUNTIF((B3:U3),(">"&$A$1))

this will give you a count of overdue dates for each of the employees.
Now just run autofilter to show values greater than "0" and you have your summary ni the same sheeet.

Hope this helps,
Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.