Pie Chart Disappears after recalculating

[roaml]

Hello,

I am tearing my hair out over this one…

First, I have a Conditional Sum formula (see below) that I am using to count the number of times a name appears in a particular column. The column I am counting from is in tab “A_Shift”. This actually works pretty well!

Formula: =SUM(IF(A_SHIFT!$B$2:$B$20000=C4,1,0))


Second, I do a total sum of the number of items located by the Conditional formula (see below). This is also working well.

A Shift 34
B Shift 24
C Shift 20


Next, I created a pie chart graph showing the shift, total and percentages. This also works well.

So, all of the above works well, but each time I repopulate the report and get new totals, the graph appears blank! I have to recreate the graph over and over. I would like the pie chart to open in its original state with the percentages and totals.

Why does the pie chart disappear?

Very frustrating :-(

Thank you.

 

[SkipVought]

but each time I repopulate the report and get new totals

How are you repopulating the report? Please explain the process. Are you deleting rows or columns? How are you referencing the source data range for the chart?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[roaml]

Hi Skip,

I apologize for the lack of informaiton.

I am exporting data from a Access query using a criteria form.

Initially, the queried data is exported to the excel report. When I export the data again, the worksheet is cleared from its previous data and then repopulated with the new data. So, the code is written to clear the contents prior to exporting the new contents.

 

[SkipVought]

The CLEARING is the problem.

Rather than EXPORT from Access, Query Access FROM Excel via Data/Get External Data/New Database Query -- Access files.....

Use DYNAMIC ranges to define your series.

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[pbrodsky]

Actually, if you just do a simple Data | Import External Data... | Import Data, Excel appears to name the range of the imported data automatically.

 

[SkipVought]

Excel names BOTH query and import ranges.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[roaml]

Good, I will try your suggestions.

Thanks for the responses.

%-)