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.