Oct 19 2019 08:34 AM
I'd be amazed if this could be done.
I have 120 staff and their attendance records. I want to create a page for each person showing their attendance in a pie chart as well as the numbers underneath.
I don't want to have to do it all manually. Is there a way to do this using a combinations of formulae? mail merge? reports??
Any help would be really appreciated!
Oct 20 2019 04:25 AM
There are several ways that you can use for this. For this type of situation, I usually use VBA to create a macro that loops through the list of employee names, and for each one:
- filters the data,
- updates a template sheet that has the structure for your report,
- saves that page as PDF
Then, you can send each employee their PDF report. It is also possible to automate this, so that you don't have to create and send each email manually.
If you need to have a separate sheet on your file for each employee, that can also be done with macros. Usually, that report template sheet is enough. If at a certain moment you want to look at an employee's report, you can use the report template sheet, pick the employee name from a dropdown menu (or type it in), and choose the month, and the report will populate with the data for that employee and month.
So, yes. This is a project that takes some time to do, but it is possible to achieve what you want with Excel.
In this video, I demonstrate a system for a similar situation: https://youtu.be/DRs1Ql3Yy-c
Oct 20 2019 12:42 PM