Individual Scorecard Template

onemoremile Posts 2 Registration date Sunday September 22, 2013 Status Member Last seen October 8, 2013 - Oct 8, 2013 at 10:14 PM

Recently I have been tasked with creating individual scorecards for 300+ employees.
The data is derived from 4 .oqy cubes that are presented in pivot table format. I have
excel 2007. I would like to query the pivot tables (using macros?) and have the results
populate in a scorecard template I've created.

Problems I ran into:

-When using "getpivotdata" function to populate scorecard, I get a reference error when I change the employee in the pivot table.

-Even if I didn't get the reference error, I would have to manually display (in the pivot table) each of the 300+ employees to populate the scorecard (on another sheet in the same workbook). Cumbersome, to say the least.


-Is there a template that I am overlooking that can fix these problems? I get the feeling I am reinventing the wheel with stumbling/fumbling along with macros as I am relatively new to it.

-Is it possible to have a (relatively) simple macro to query the pivot tables? What would it look like?

-And given the previous question, is there another macro I would need to develop to populate the results of the query into my scorecard template?

I apologize if I am unclear in the details. I have been struggling with this project for a while now. It's frustrating because I have the data, I just can't get the data to populate in my scorecard without reference errors.

I really appreciate any help or pointing me in the right direction.