Hi,
I created a Performance analysis MIS report for a particular batch of trainees to measure their performance during their on-the-job-training. The report itself was supposed to be used by people not too familiar with excel (they can do basic things with clear instructions given). So I tried automating it to the best possible extent.
It contains:
1. A "Batch" sheet that lists trainee names, exact dates of training and week/month references which are linked across the workbook. So you update this sheet, the dates are updated everywhere.
2. A "Trending" Sheet which shows trainee-wise performance across various pre-set parameters for individual dates, weekly aggregate and monthly aggregates in three separate tables one below the other. The date-wise reporting pulls information from individual sheets bearing respective dates as the sheet names. The respective date-sheets themselves are created as the training period goes and not created at the beginning. The formulae in the "Trending Sheet" thus accommodates references to sheets that currently don't exist, but may come in the future. An sample formula would be:
=(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$M$8),"-",MONTH(Batch!$M$8),"-",YEAR(Batch!$M$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$M$8),"-",MONTH(Batch!$M$8),"-",YEAR(Batch!$M$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$N$8),"-",MONTH(Batch!$N$8),"-",YEAR(Batch!$N$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$N$8),"-",MONTH(Batch!$N$8),"-",YEAR(Batch!$N$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$O$8),"-",MONTH(Batch!$O$8),"-",YEAR(Batch!$O$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$O$8),"-",MONTH(Batch!$O$8),"-",YEAR(Batch!$O$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$P$8),"-",MONTH(Batch!$P$8),"-",YEAR(Batch!$P$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$P$8),"-",MONTH(Batch!$P$8),"-",YEAR(Batch!$P$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$Q$8),"-",MONTH(Batch!$Q$8),"-",YEAR(Batch!$Q$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$Q$8),"-",MONTH(Batch!$Q$8),"-",YEAR(Batch!$Q$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$R$8),"-",MONTH(Batch!$R$8),"-",YEAR(Batch!$R$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$R$8),"-",MONTH(Batch!$R$8),"-",YEAR(Batch!$R$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$S$8),"-",MONTH(Batch!$S$8),"-",YEAR(Batch!$S$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$S$8),"-",MONTH(Batch!$S$8),"-",YEAR(Batch!$S$8),"'!$4:$4")),0),FALSE),0))
$A$20-refers to the name of the trainee. The Indirect formula picks out the date from the "Batch" sheet, checks if pertains to this week. If it is, the value is all added. Sun-Sat week is used. The date-week reference is organised in a pivot in the "batch" sheet. The regular "iferror" eliminates error for those sheets/dates that don't exist now, while accommodating them as they are created later on. It works because I'm able to organise the dates for a week and it is only 7 days in a week (fixed).
3. I need to collate data in a similar manner for the respective month. I can't seem to build a logic to do that, I tried the long way, unfortunately the formula is 9390 characters long and Excel only accommodates 8192 characters in a formula.
What I need:
I need a vba code that does the following:
1. Check a date from a list of 40 days (a training doesn't last more than 40 days). If it pertains to month 1 (say october), collate the information under month 1 (october) from the sheet with the date name, else ignore it. The dates are in a single column, but not in ascending order (jumbled).
2. Go to the next cell and repeat this for all the 40 days in the list.
Needless to say, I have never written a code or macro or VBA, so any help would be greatly appreciated. If you need, I could send the entire excel file (1.5 MB) for your perusal. You can mail me at ssivakumar@outlook.com
Regards,
Siva Kumar.
I created a Performance analysis MIS report for a particular batch of trainees to measure their performance during their on-the-job-training. The report itself was supposed to be used by people not too familiar with excel (they can do basic things with clear instructions given). So I tried automating it to the best possible extent.
It contains:
1. A "Batch" sheet that lists trainee names, exact dates of training and week/month references which are linked across the workbook. So you update this sheet, the dates are updated everywhere.
2. A "Trending" Sheet which shows trainee-wise performance across various pre-set parameters for individual dates, weekly aggregate and monthly aggregates in three separate tables one below the other. The date-wise reporting pulls information from individual sheets bearing respective dates as the sheet names. The respective date-sheets themselves are created as the training period goes and not created at the beginning. The formulae in the "Trending Sheet" thus accommodates references to sheets that currently don't exist, but may come in the future. An sample formula would be:
=(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$M$8),"-",MONTH(Batch!$M$8),"-",YEAR(Batch!$M$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$M$8),"-",MONTH(Batch!$M$8),"-",YEAR(Batch!$M$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$N$8),"-",MONTH(Batch!$N$8),"-",YEAR(Batch!$N$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$N$8),"-",MONTH(Batch!$N$8),"-",YEAR(Batch!$N$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$O$8),"-",MONTH(Batch!$O$8),"-",YEAR(Batch!$O$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$O$8),"-",MONTH(Batch!$O$8),"-",YEAR(Batch!$O$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$P$8),"-",MONTH(Batch!$P$8),"-",YEAR(Batch!$P$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$P$8),"-",MONTH(Batch!$P$8),"-",YEAR(Batch!$P$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$Q$8),"-",MONTH(Batch!$Q$8),"-",YEAR(Batch!$Q$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$Q$8),"-",MONTH(Batch!$Q$8),"-",YEAR(Batch!$Q$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$R$8),"-",MONTH(Batch!$R$8),"-",YEAR(Batch!$R$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$R$8),"-",MONTH(Batch!$R$8),"-",YEAR(Batch!$R$8),"'!$4:$4")),0),FALSE),0))+(IFERROR(VLOOKUP($A20,INDIRECT(CONCATENATE("'",DAY(Batch!$S$8),"-",MONTH(Batch!$S$8),"-",YEAR(Batch!$S$8),"'!$A$1:$I$35")),MATCH(B$17,INDIRECT(CONCATENATE("'",DAY(Batch!$S$8),"-",MONTH(Batch!$S$8),"-",YEAR(Batch!$S$8),"'!$4:$4")),0),FALSE),0))
$A$20-refers to the name of the trainee. The Indirect formula picks out the date from the "Batch" sheet, checks if pertains to this week. If it is, the value is all added. Sun-Sat week is used. The date-week reference is organised in a pivot in the "batch" sheet. The regular "iferror" eliminates error for those sheets/dates that don't exist now, while accommodating them as they are created later on. It works because I'm able to organise the dates for a week and it is only 7 days in a week (fixed).
3. I need to collate data in a similar manner for the respective month. I can't seem to build a logic to do that, I tried the long way, unfortunately the formula is 9390 characters long and Excel only accommodates 8192 characters in a formula.
What I need:
I need a vba code that does the following:
1. Check a date from a list of 40 days (a training doesn't last more than 40 days). If it pertains to month 1 (say october), collate the information under month 1 (october) from the sheet with the date name, else ignore it. The dates are in a single column, but not in ascending order (jumbled).
2. Go to the next cell and repeat this for all the 40 days in the list.
Needless to say, I have never written a code or macro or VBA, so any help would be greatly appreciated. If you need, I could send the entire excel file (1.5 MB) for your perusal. You can mail me at ssivakumar@outlook.com
Regards,
Siva Kumar.
