Pie chart report

%3CLINGO-SUB%20id%3D%22lingo-sub-919748%22%20slang%3D%22en-US%22%3EPie%20chart%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-919748%22%20slang%3D%22en-US%22%3E%3CP%3EI'd%20be%20amazed%20if%20this%20could%20be%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20120%20staff%20and%20their%20attendance%20records.%20I%20want%20to%20create%20a%20page%20for%20each%20person%20showing%20their%20attendance%20in%20a%20pie%20chart%20as%20well%20as%20the%20numbers%20underneath.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20want%20to%20have%20to%20do%20it%20all%20manually.%20Is%20there%20a%20way%20to%20do%20this%20using%20a%20combinations%20of%20formulae%3F%20mail%20merge%3F%20reports%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20really%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-919748%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-920395%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20chart%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920395%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F429362%22%20target%3D%22_blank%22%3E%40Frankgee82%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20several%20ways%20that%20you%20can%20use%20for%20this.%20For%20this%20type%20of%20situation%2C%20I%20usually%20use%20VBA%20to%20create%20a%20macro%20that%20loops%20through%20the%20list%20of%20employee%20names%2C%20and%20for%20each%20one%3A%3C%2FP%3E%3CP%3E-%20filters%20the%20data%2C%3C%2FP%3E%3CP%3E-%20updates%20a%20template%20sheet%20that%20has%20the%20structure%20for%20your%20report%2C%3C%2FP%3E%3CP%3E-%20saves%20that%20page%20as%20PDF%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20you%20can%20send%20each%20employee%20their%20PDF%20report.%20It%20is%20also%20possible%20to%20automate%20this%2C%20so%20that%20you%20don't%20have%20to%20create%20and%20send%20each%20email%20manually.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20need%20to%20have%20a%20separate%20sheet%20on%20your%20file%20for%20each%20employee%2C%20that%20can%20also%20be%20done%20with%20macros.%20Usually%2C%20that%20report%20template%20sheet%20is%20enough.%20If%20at%20a%20certain%20moment%20you%20want%20to%20look%20at%20an%20employee's%20report%2C%20you%20can%20use%20the%20report%20template%20sheet%2C%20pick%20the%20employee%20name%20from%20a%20dropdown%20menu%20(or%20type%20it%20in)%2C%20and%20choose%20the%20month%2C%20and%20the%20report%20will%20populate%20with%20the%20data%20for%20that%20employee%20and%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20yes.%20This%20is%20a%20project%20that%20takes%20some%20time%20to%20do%2C%20but%20it%20is%20possible%20to%20achieve%20what%20you%20want%20with%20Excel.%3C%2FP%3E%3CP%3EIn%20this%20video%2C%20I%20demonstrate%20a%20system%20for%20a%20similar%20situation%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FDRs1Ql3Yy-c%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FDRs1Ql3Yy-c%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-920730%22%20slang%3D%22en-US%22%3ERe%3A%20Pie%20chart%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920730%22%20slang%3D%22en-US%22%3EThat%20looks%20a%20bit%20above%20my%20skill%20level%20unfortunately...%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20(or%20anyone%20reading%20this)%20could%20set%20me%20a%20spreadsheet%20up%20which%20can%20do%20what%20I%20need%20by%20inserting%20the%20data%20and%20running%20the%20macro%20I'd%20be%20forever%20grateful!!!%3CBR%20%2F%3E%3CBR%20%2F%3EFrank%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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!

2 Replies
Highlighted

@Frankgee82 

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

 

This video shows an example of how Excel can be automated using VBA to allow the user to save time while Excel takes care of repetitive tasks by itself.
Highlighted
That looks a bit above my skill level unfortunately...

If you (or anyone reading this) could set me a spreadsheet up which can do what I need by inserting the data and running the macro I'd be forever grateful!!!

Frank