I currently need 2 excel spreadsheets to do some number crunching, automatic field population, highlighting etc.
Need someone who is a whiz at exel, knows how to build formulas, lock certain parts down, create report/sorting in excel that is locked down, and has experience in making excel accept data from outside, and manipulate it to a certain outcome. I've speced out what needs to be done below (obviously once the project is accepted I'll be able to provide the raw data and expand further) - if you can do it, please bid - need it done quickly.
The spreadsheets will be used to give contractor trainers a list of students they will need to manage and create checkpoints of next contact dates, as well as consolidate data pasted into it, to create summary information and alerts if a student hasn't logged in for a while
The basic columns are already there, and they contact student information such as Name, number, email course enrolled.
The requirement is to create:
1. one extra column that asks if an induction call has been made (default is no, can be changed to yes)
2. another column that requests date of when call was made: format: dd/mm/yyyy
3. another 4 column that will automatically put in a date +30 days, +60 day, +90 days , +120 days from date of when initial call was made.
4. after each date column, it is requested from a trainer to input comments from their conversation
5. a column at the very end that captures: " Qualification Completed" -yes/no
The only data that can be modified by a trainer is the "induction call made", "date call made", and "comments"
Since this spreadsheet is going to be a few hundred lines of students, and is going to be a 'live' spreadsheets. More students will be added it it weekly.
Something needs to be built into it that allows a trainer query the information weekly and find out who whey need to contact in a given timeframe (i.e they might only run it once a week and look for all people they need to contact in that week, or they need to find all students that yet have not have not yet had an induction call made.
A raw report from our student management system needs to be dropped into this spreadsheet so that it 'does its thing'.
The raw data has 6 columns: Student Full Name, Unit Studying, Trainer, Type, Unit Completion % and Last Logged in date (this is what is shown in the last column as one of the results: Thu Jun 09 16:52:39 UTC 2011)
A student doing a qualification with 4 units, will appear as 4 separate lines, and the number of units a student can be doing varies from 1 to 13 (see example below:)
Student Full Name | Unit Studying | Trainer | Type | Unit Completion % | Last Logged in date
John Doe BSBHRM406A Bob Smith Full 13% Thu Jun 09 16:52:39 UTC 2011
John Doe BSBBSB408A Bob Smith Full 0% Thu Jun 09 16:52:39 UTC 2011
John Doe BSBHRM409A Bob Smith Full 2% Thu Jun 09 16:52:39 UTC 2011
John Doe BSBHRM404A Bob Smith Full 6% Thu Jun 09 16:52:39 UTC 2011
What the spreadsheet does with this data is as follows:
1. sort all students so that they are grouped together, then sorted by trainer.
2. once all data is sorted properly, to minimize the multiple student rows into one row, and give an average of current student completion as a percentage.
3. if last login date is greater than 30 days and/or the unit completion average =0%, it needs to be flagged somehow in red
4. all red flagged need to be sorted together
5. extra column created to capture contact date with student + capture comment in another column- changing color status