Create a list of courses with a month filter

petethecat

Board Regular
Joined
Oct 25, 2011
Messages
63
Hello All

I have a long list of course data from which i want to create a list of the courses that were available against a month that i pick. In turn this will enable me to create a graph against the month.


This is how the data looks at present (This is not the complete list!)
Date of courseName Of CourseDelegate DepartmentPlanned number of attendeesActual Number of Attendees
02/04/2012Manual HandlingFC11
02/04/2012Manual HandlingCS22
02/04/2012Manual HandlingWC33
02/04/2012Manual HandlingEngineering42
02/04/2012Manual HandlingProcess Tech55
02/04/2012Manual HandlingQuality51
02/04/2012Manual HandlingCustomer Services1
02/04/2012Manual HandlingCI44
02/04/2012Manual HandlingHR31
01/05/2012Leadership - managing performance problemsFC21
01/05/2012Leadership - managing performance problemsCS22
01/05/2012Leadership - managing performance problemsWC33
01/05/2012Leadership - managing performance problemsEngineering42
01/05/2012Leadership - managing performance problemsProcess Tech55
01/05/2012Leadership - managing performance problemsQuality51
01/05/2012Leadership - managing performance problemsCustomer Services1
01/05/2012Leadership - managing performance problemsCI44
01/05/2012Leadership - managing performance problemsHR11
01/05/2012Fire Extinguisher TrainingAdmin10
02/05/2012Discipline & GrievanceFC43
02/05/2012Discipline & GrievanceCS98
02/05/2012Discipline & GrievanceWC54
02/05/2012Discipline & GrievanceEngineering33
02/05/2012Discipline & GrievanceProcess Tech1
02/05/2012Discipline & GrievanceLabs11

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=2></COLGROUP>


So what i want to do is say that the month is April (01/04/2012 to 30/04/2012) & create a list of courses that were available during that date range. I have tried Pivot tables but they aren't really doing what i want them to do! - though i have to say I am a TOTAL novice at pivot tables!

Any suggestions will be gratefully recieved

Petethecat
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
A pivot table would work if you group the dates by month in the pivot table and then filter only for, say April.

An easier way is to turn on Autofilters (Select all the cells in your database, then click Data-Filter) and filter the dates for just April. In case you're not familiar with filters this link may be helpful: Excel AutoFilter or Advanced Filter? | Contextures Blog
 
Upvote 0
A pivot table would work if you group the dates by month in the pivot table and then filter only for, say April.

An easier way is to turn on Autofilters (Select all the cells in your database, then click Data-Filter) and filter the dates for just April. In case you're not familiar with filters this link may be helpful: Excel AutoFilter or Advanced Filter? | Contextures Blog

Yes a filter would create the concise list against the month - however it would be in the wrong place. I am creating a staging area using sumifs to extract the data i want. One of the criteria is to filter the course (Column B) with the identifying criteria being the course names. This is easy to do i have a seperate list of all the courses are selected via a pulldown menu - which keeps spelling errors out thus meaning the formula works OK.


So if it is possible I want to use the staging area to create the list - a bit like this:

Training Attendance By Course for: FastCatCSFWashCoatEngineering
May-12PlannedActualPlannedActualPlannedActualPlannedActual
Manual Handling00000000
Leadership - managing performance problems21223342
Discipline & Grievance43985433
Leadership - setting performance expectations11440011
Fire Extinguisher Training11002111
Mats Training11111100

<TBODY>
</TBODY><COLGROUP><COL><COL span=8></COLGROUP>


So the numbers are the sumifs formula - which work OK & the "MAY-12" bit is where i pick the month. So underneath the month is where the courses for that month are automatically listed.

By doing it this way i can create a graph from the data that shows the YTD attendences (i already have this working perfectly) & then a seperate graph that shows the Months figures (the month of my choosing)

I guess what i want to do is create a list of course done for the month but i am at a loss as to how i extract that bit of data. I have looked at Pivot tables (actually kinda worked - just in the wrong area!) VLOOKUPS - I even looked at INDEX but non seem ideally suited for the task in hand.

I really appreciate your help so far - if you have anything more that you can add that would be a life saver!!!
 
Upvote 0
I may not be following correctly, but it sounds like you have everything for the table in your last post except the names of the courses that were held in the selected month. So for May you want Manual Handling to not be listed because 0 people attended but you want the other courses listed that had attendees - is that right or am I missing something?
 
Upvote 0
I may not be following correctly, but it sounds like you have everything for the table in your last post except the names of the courses that were held in the selected month. So for May you want Manual Handling to not be listed because 0 people attended but you want the other courses listed that had attendees - is that right or am I missing something?


That is exactly right

Like i said i kind of have a Pivot table working which will enable me to filter for one month - with a bit of faffing about!

It would be really great to be able to select two months - lets say April & May & see what courses we held during that time & the attendance figures. Trouble is that when you choose two months it changes the description in the pivot table box to "selection" which stops the sumifs formula from working.

That is where i am stuck & would like to hear any ideas

Cheersloads
Petethecat
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top