Hi, looking for some help with a CALC problem I’m facing…
I’m trying to automate the process of pulling data from a pivot table to create/update a graph automatically. I’m also looking to have the raw response data that feeds the pivot table update as it gets entered or via a button/macro.
I read a post somewhere about creating a Pivot Table Refresh button, which I now have in my toolbar. But as stated I would like to automate the entire process.
I’ve tried recording a macro with the mouse and menu/toolbar options but it hasn’t worked as I expected a macro to work. Here is what I’ve got so far…
• Raw Response Data (a grid that won’t grow inline items, so this is a fixed quantity, I’ll just be updating the responses over time i.e. there will only ever be 100 responses)
• Pivot Table
• An Extraction Table (values from the pivot table, copied with a simple = formula)
• Graph Table (Extraction Table data without formulas, manually populated via ‘Paste Special = Values’ to strip out the formulas from the Extraction table
• The Graph - drawn from Graph Table data in Sheet 2
Am I going about this the wrong way? I welcome some guidance/advice. Thanks!