What I want: </SPAN>
I have a workbook with 12 worksheets- each worksheet has a table for a month of the year—each table has the exactly the same headings: Employee number and 10 performance measurements.
</SPAN>
What I would like to do is have a workbook that my manager can open and from the “home page” that she opens, I want her to be able to easy construct whatever scenario she wants: any month, any employee, and any performance measurement. I also want her to be able to see how one employee has been doing year to date by seeing all months data for the same employee, as well as see how all employees compare to each other for all months. In the last scenario, I want to use conditional formatting to use color to quartile how the employee performed in that month compared to all employees (that way, when looking at one employee, she can know how they did, and know relatively how that employee performed compared to the other employees).
</SPAN>
What I have already done and the limitations I want to overcome: </SPAN>
1. I have created pivot tables/charts/slicers so my manager can select the employee(s), the month(s), and the performance measurement(s) that she wants to see, however, charts are not the best way to represent the data. Visually, she wants to see a table (not pivot table, I’ll explain more later). If I could create a slicer for a table (again, not a pivot table) as a “master filter”, I believe my work would be done, but alas, unless I’m wrong, this is not possible.</SPAN>
The other problem with charts is that I am potentially working with data to represent 400+ employees for 12 months for 10 different performance measurements, so that’s 48,000 individual pieces of information that would be virtually impossible to represent on a chart, hence, I want a table.
</SPAN>
2. I have looked into using a pivot table and slicer (essentially a report filter), but the pivot table has limited functionality in terms of being able to filter/sort individual performance measurements, etc.
</SPAN>
-Side note: if I use a pivot table, my manager can change views between looking at one employee and multiple months vs one month with multiple employees by changing the order of fields for Row Labels. Can I create a “button” that will give her this option so she doesn’t have to go into the field list?
</SPAN>
I am aware that Access may be a better application to use for the purposes of a “3-D” table, which is essentially what I’m trying to do. I don’t know Access very well, and if there is a solution for Excel, I would like to stay in Excel. However, if it really is something I should pursue, I would appreciate feedback in terms of any features or functions that I would need to utilize in Access (I can research how to use them, etc), or reference a guide/tutorial would be awesome.</SPAN>
I don’t know VBA very well, though I have a feeling that may be the only way to achieve what I want. Again, any tutorials/references that are as specifically geared towards this purpose would be very helpful. </SPAN>
Thank you for any advice!</SPAN>
I have a workbook with 12 worksheets- each worksheet has a table for a month of the year—each table has the exactly the same headings: Employee number and 10 performance measurements.
</SPAN>
What I would like to do is have a workbook that my manager can open and from the “home page” that she opens, I want her to be able to easy construct whatever scenario she wants: any month, any employee, and any performance measurement. I also want her to be able to see how one employee has been doing year to date by seeing all months data for the same employee, as well as see how all employees compare to each other for all months. In the last scenario, I want to use conditional formatting to use color to quartile how the employee performed in that month compared to all employees (that way, when looking at one employee, she can know how they did, and know relatively how that employee performed compared to the other employees).
</SPAN>
What I have already done and the limitations I want to overcome: </SPAN>
1. I have created pivot tables/charts/slicers so my manager can select the employee(s), the month(s), and the performance measurement(s) that she wants to see, however, charts are not the best way to represent the data. Visually, she wants to see a table (not pivot table, I’ll explain more later). If I could create a slicer for a table (again, not a pivot table) as a “master filter”, I believe my work would be done, but alas, unless I’m wrong, this is not possible.</SPAN>
The other problem with charts is that I am potentially working with data to represent 400+ employees for 12 months for 10 different performance measurements, so that’s 48,000 individual pieces of information that would be virtually impossible to represent on a chart, hence, I want a table.
</SPAN>
2. I have looked into using a pivot table and slicer (essentially a report filter), but the pivot table has limited functionality in terms of being able to filter/sort individual performance measurements, etc.
</SPAN>
-Side note: if I use a pivot table, my manager can change views between looking at one employee and multiple months vs one month with multiple employees by changing the order of fields for Row Labels. Can I create a “button” that will give her this option so she doesn’t have to go into the field list?
</SPAN>
I am aware that Access may be a better application to use for the purposes of a “3-D” table, which is essentially what I’m trying to do. I don’t know Access very well, and if there is a solution for Excel, I would like to stay in Excel. However, if it really is something I should pursue, I would appreciate feedback in terms of any features or functions that I would need to utilize in Access (I can research how to use them, etc), or reference a guide/tutorial would be awesome.</SPAN>
I don’t know VBA very well, though I have a feeling that may be the only way to achieve what I want. Again, any tutorials/references that are as specifically geared towards this purpose would be very helpful. </SPAN>
Thank you for any advice!</SPAN>
