Creating a progress chart

**kenhjh** · 07-09-2010, 04:50 AM

I'm running Excel 2003 and I'm working on a training progress chart for over 5,000 participants which has me pretty much stumped at this point.

The raw data that I have goes something like this:

A B
Name Course

What I want as the end result is something that allows me to track their progress automatically. The problem lies in that there are multiple certification levels, each with their own criteria. For example, certification level 1 requires several criteria (let's call them A, B, C and D) to be fulfilled. Within each criteria, there are certain courses (1, 2, 3, 4, 5 etc.) that must be attended to meet that criteria. Some courses may fulfill more than one criteria while others only fulfill one criteria.

Now, I need as the end product is a list with their names in Column A and their current certification levels (based on the criteria met, which is in turn based on the courses attended). So far I've managed to extract a unique list of names from the raw data down Column A, and a list of criteria (not courses) in Row 1 in my Certification table.

I'm trying to find an efficient formula that would make use of the corresponding name + criteria (not course) in any particular cell in the certification table, use a lookup function to see if the courses that fit that criteria exists in the raw data, and return a 1 or 0 function. Subsequently its just a simple series of IF functions to determine their certification levels.

Hope my explanation's clear enough. So yeah, if anyone can help I'd be really grateful! Thanks in advance

**teylyn** · 07-09-2010, 05:46 AM

Hello Ken,

welcome to the forum!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

It looks to me that you will need to enter on a per person basis each course/level/certification/date/whatever

in a table with a column for each of these criteria. The it is a snap to do the analysis with a pivot table or pivot chart.

**kenhjh** · 07-11-2010, 10:27 PM

Hi Teylyn,

Thanks for the help! I've attached a sample with this post: the raw data is contained within the "Data" worksheet (only the essential columns, my original has other columns such as date and duration but essentially I only need to know if the course exists), with the desired end product in the last worksheet.

I've toyed around with pivot tables but my concern is that the criteria list changes frequently, resulting in the formatting being messed up, and also the problem of not updating automatically. It should also be as user-friendly as possible since this will mainly be used by people with minimal excel knowledge. As new information comes in, one should only have to update the "Criteria" worksheet and see the changes reflected in the final product.

Thanks again!