Subscribe to learn more about Excel Spreadsheet and Excel VBA.
Subscribe to learn How to create Dynamic Weekly Report without using VBA in Excel?
Weekly report is one of the periodical reports. There is a monthly report or yearly report. Knowing of how to create a weekly report will help us of how to create the other similar reports. So it is worth to learn the technique. This technique can also be useful for similar task not just only reports.
Creating a weekly report is a tedious work. You have to compile all the data This video show you the special technique to how to create Dynamic Weekly Report without using VBA in Excel. First, we will create the details report, and then we will create the summary report. We will learn how to use Excel functions and formula to create this beautiful technique. It is likes cooking. You have learned most of the functions and formulas in Excel. But the beauty is how to cook this functions and formulas to become program of its uniqueness.
Subscribe not only to learn about dynamic weekly report, but also to learn more of the techniques in Excel Spreadsheet and Excel VBA which I will upload in the future.
The technique of how to create dynamic weekly report is as below.
Technique
1. Create 3 sheets with names as dataentry, weekly details report, weekly summary report and computation. That means we need only 4 sheets to perform the job.
2. The dataentry sheet is for you to key in all the information.
3. The rule of thumb in key in these particulars is that the date data must be in ascending order.
4. The weekly details report is the report you wanted for detail summary report. Key in the week number cell in cell H1.
5. Then create a dynamic text in cell A1 for the title.
6. Create headings similar to the dataentry sheet headings.
7. The weekly summary report is the report you wanted for summary report.
8. Create a dynamic text for the report title related to cell A1 of the weekly details report sheet.
9. Create headings for customer and amount.
10. The computation sheet is used to create your hidden formulas for the dynamic weekly report to work. Once completed, usually it is hidden and cannot be seen by users.
11. Link the cell H1 of the weekly details report to the cell A1 of the computation sheet.
12. Assign week number to all the date accordingly. This we make use of the WEEKNUM function.
13. Perform multi-pointer to select the row positions related to the given week. This we make use of the IF function.
14. Compress the data in step (13). This we make use of SMALL function. We also make use of the IFERROR function to perform blanking.
15. Extract all data accordingly. This we make use of the HLOOKUP function.
16. The extracted data is ready for the details report. Link the respective data to the weekly details report sheet accordingly.
17. Test with different week number.
18. Now we have completely done with the weekly details report.
19. For the weekly summary report, go back to the computation sheet.
20. Create a frequency count for customer from the extracted data. This we make use of COUNTIF.
21. Perform multi-pointer to select the frequency count of 1. This we make use of IF function.
22. Compress the list. This we make use of SMALL function.
23. Extract all related data for customer only. This we make use of HLOOKUP function.
24. Create formula to obtain the total amount for respective customers. This we make use of SUMIF function.
25. Link the extract result to weekly summary report sheet accordingly.
26. Go to weekly details report sheet to perform testing.
It is worth to learn about this dynamic weekly report. It will surely help you in your presentation. Please enjoy yourself in the video.
Subscribe to learn on How to create Dynamic Weekly Report without using VBA in Excel Spreadsheet.
