In order to consolidate report files into one without copy-pasting is very easy. In fact, I’ve been using this trick ever since I was tasked to summarize the reports of 600+ schools in our city.
Imagine handling more than 600 files and coming up with a single report to explain the data. This task would normally take weeks or even months to accomplish.
But if all the files are available and are ready for consolidating, this would only take me a day to finish. This includes finalizing a chart dashboard report that is both flashy and corporate looking.
I’ve uploaded a YouTube video tutorial to explain this trick in the simplest manner. You can find the video below and see how fast I can consolidate 40 files into one.
The VBA Codes I use for this trick is available below. Since our website restricts copy-pasting due Intellectual Property Rights (IPR), a download link is provided for all of you by clicking here.
'Code Print https://ischoolforms.tech
'ref Integrated School Forms 2019
Path = ""
FileName = Dir(Path & "*.xlsx")
Do While FileName <> ""
Workbooks.Open FileName:=Path & FileName, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
FileName = Dir()
If you have any other questions or comments regarding this Consolidate Report Files trick, then please write your comments below.
If you’re having trouble finding a solution how to install MS Office 2016, and considering it is a paid software, we opted to provide you – for educational purposes only – (^_^) a procedure to which you can download and install MS Office 2016 on your PC or laptops.
This tutorial is only applicable End of School Year (EoSY) 2016 – 2017. Since we are having issues with updating/uploading the final grades of our students on the LIS database for some unknown reason. But since we need to submit credible, valid and reliable School Form 5 (SF5 – Report on Promotion and Level of Proficiency & Achievement) the easiest way is to actually allow Excel to do the work for us. And here on Integrated School Forms, we’re going to teach you how to do that.
Step 1: Open your Summary – Final Grades Excel Sheet from the E-Class Record you used for this School Year. (the image below is just a sample summary grading sheet I made, the scores are fictitious in nature, and was only uploaded for demonstration purposes only).
Step 2: Open you School Form 5 which you downloaded from the LIS website as well, so that both Excel files are active.
Step 3: Go to Column G – Row 14 which is known as cell G14 and this is where we will type the formula. The formula may differ depending where your files were saved, that is why we need to open both files at the same time in order for excel to fetch the sources regardless where it was saved.
Step 4: This step will have substeps so please follow the sub-steps below to fetch the data.
4.1 Type in Cell G14 the following first few codes ” =vlookup(C14, ” The purpose for this formula is to vertically look for (vlookup) the name of ASAKIL (found on C14) and the comma ” , ” means that we have closed the first function of the formula but we have not ended our command yet.
4.2 After you finished typing the above formula, DO NOT PRESS ENTER just yet, rather press ALT and TAB keys on your keyboard so that you will be able to keep the formula active and transfer to your Second File which is the Summary of Final Grades Excel Sheet. Once you are on the Summary of Final Grades Sheet, click on Cell B11 (which is the first name of the boys row) and Press and Hold the Shift Key on your keyboard up to the last column-row of the girls which is actually Cell AU111. This is the same for all since the Summary Sheet is designed uniformly, all the cells will always the same numbers and rows.
4.3 After that, TYPE the following ” ,46,FALSE) ” and press ENTER on your Keyboard. You will be transferred back to your School Form 5 sheet and the grade of that Student will automatically show. Just like the following images below:
Step 5: Inspect the cells under your Column G if any of the cells are merged cells or not. Since the SF5 is a generated excel sheet from the LIS, there are instances where a row of a student is actually a merged cell. In this case for example, our first student that has our formula is actually a merged cell of G14 and G15. We can see this from the ribbon tab that our Merge & Center button is grayed. In order to fix this, highlight the cells from G14 down to the last cell of the male student you have. Here on our example, since the class has 18 male students, we will be highlighting the cell G14 to G41. Once all the cells are highlighted, click the Merge & Center button to unmerge all cells. press CTRL and D on your keyboard to generate the same formula down to the last student. You might notice that after you press the Ctrl and D keys, there are some cells that has values which are #N/A, these cells were the unmerged cells that didn’t contain any particular name on the left side. What we need to do now is to delete all the cells that has an #N/A value. We can do this with two possible ways, Option 1: we can edit the formula in G14 by adding an IfError function after the equals sign to have:
=IFERROR(VLOOKUP(C14,'[GRADE 4-6_SUMMARY FINAL GRADES.xlsx]SUMMARY – FINAL GRADES’!$B$11:$AU$111,46,FALSE),””)
and then highlight again G14 down to the last cell of the male. Or, Option 2, we can just highlight the cells that have #N/A and the cell above it and click the Merge & Center again. There will be a prompt window after than, just click OKAY. And then edit the cell boarders right after.
Once you are finished with (either) merging the cells or changing the formula of G14, you can proceed with fixing the borders for a cleaner look on your SF5. You can follow the image below if you don’t know how. The procedure is very easy.
Now you have fetched the grades of your students from your Summary Final Grading Sheet to your downloaded SF5 from the LIS. You can repeat the same process for your female list of students, and I’m pretty sure you find this quite difficult at first but believe me, once you tried this once or twice, you’ll get the hang of it and would prefer to do this instead of manually typing the grades from your Summary Sheet to your SF5.
I hope this could be of helped since we can’t encode the grades yet on our LIS database.
For classes that have honor students, you’ll need to have the fetched grades with decimal places. This is where the left side of the Summary Grading Sheet comes in handy. As you can see on your Summary Grading Sheet, the Final Grade of each student actually has a 2 decimal placed grade on the left side column. This could be fetched directly in the SF5 just by adding a little tweeking on your formula. The formula we used above was:
=VLOOKUP(C14,'[GRADE 4-6_SUMMARY FINAL GRADES.xlsx]SUMMARY – FINAL GRADES’!$B$11:$AU$111,46,FALSE)
This formula is the one we used on cell G14, in order to change the into decimal places, we need to fetch the left column of our Summary Final Grades, so we add a state of condition in our formula that would be if the grade is greater than 89.99, then fetch the grade on the left side, but if the grade is lower than 90, then remain as is. To convert this into a formula, we have:
=IF(VLOOKUP(C14,'[GRADE 4-6_SUMMARY FINAL GRADES.xlsx]SUMMARY – FINAL GRADES’!$B$11:$AU$111,46,FALSE)>89.99,VLOOKUP(C14,'[GRADE 4-6_SUMMARY FINAL GRADES.xlsx]SUMMARY – FINAL GRADES’!$B$11:$AU$111,45,FALSE),VLOOKUP(C14,'[GRADE 4-6_SUMMARY FINAL GRADES.xlsx]SUMMARY – FINAL GRADES’!$B$11:$AU$111,46,FALSE))
The image below shows the formula above now fetching the grade of the first student that is greater than 89.99.
The file has automated the dates for SY 2017 – 2018, thus there is no need to edit the dates of each month. If there will be changes for the date of the Last Day of Service, please change the date of the EoSY which is found below your name.
Need a monitoring tool for DLL/LP for the whole School Year? With an automated date generator? Then you’re in the right place!
At the bottom of the file, the name of the Rater and School Head
Indicate the scores per box in the specified dates between scores of 1 to 5 (1 being the lowest and 5 as highest). The rubrics for this is based on the KRA in the agreed IPCRF in your school/district/division.
Once all entries are filled, the template will automatically generate your Rate for the End of the School Year. You can also track the scores of your DLL/LP every week as the Total and Rating per week is also indicated.
The template is also ready to print. Just be sure to use 8.5″x13″ bond paper. No need to adjust anything in your printer settings since the file is already set for you.
The DLL/LP Monitoring tool is very useful and could also be used in your IPCRF as an attachment (Means of Verification – MOV) for your score. It also presents credible transparency between the rater and the ratee.
Share this post
Share our website and earn
error: Content is copyrighted, please contact website administrator.