Pivot Table Query

[ColmR]

Hi,
I am currently generating reports/graphs in excel from downloaded data and the list of what different people would like to see on the report is growing by the day. My problem is that the excel file has now reached 74MB and calculation time is over 10 mins.
The question I have is that I reckon I can reduce the file size by generating graphs using pivot tables but I would like to select the data people view from a Main Navigation Page. For example, I would like to have one Graph capable of displaying different data depending on what different people would like to see but I do not want them to change the configuration of the pivot table.
Any help on this would be greatly appreciated. Regards, ColmR

 

[SkipVought]

Hi,

YES! I do this. Rather than having 200 chart sheets, you can have ONE, with some controls so that users can choose the data they need to view.

You can have a worksheet with an embedded chart or a chart sheet. If you want to use Control Toolbox controls, you must use them on a worksheet, with an embedded chart. Forms controls can be placed in a chart. I most often use chart sheets with Forms controls.

You may be able to do some things completely without macros, but the more complex is your control design, you'll probably need to use some code.

Get John Walkenbach's book on Excel Charts. I refer to this often. Lots of different chart examples and a section on this topic.

Here's a brief outline of how I might design the control structure for a chart that displays data based on a list of values.

1. Make a unique list, using either MS Query (I most often use) or Advanced Filter.

2. Place a ComboBox in the chart, with the list source as the unique list. Link to a cell on your chart data sheet, named SelectedValueIndex. Adjacent cell named SelectedValue, using the INDEX function, referencing the unique list.

3. Use the SelectedValue in formulas to change the values displayed in the chart data sheet, usually a SUMPRODUCT or some other aggregation formula.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}