Dynamic Chart (OFFSET Function) plots empty cells.

[SJ]

I have read the posts about Excel charts plotting cells that are
empty. I'm not sure I understood what I read, and our particular
circumstances may be a bit different. (I'm not sure.)

Anyway. Our problem is:
We use Excel 2003 MSQuery to extract the results of an Access query
and automatically put them in an Excel spreadsheet. Depending on the
circumstances, the query doesn't always return the same amount of
data. In order to automatically expand or shrink the range of cells
that actually contain data, we use the OFFSET function
DonationsTotals=OFFSET(Donations!$K$2,0,0,COUNTA(Donations!$K:$K),1).
From this data, we have a chart that is supposed to plot only the
cells that are not empty: =NewReport.xls!DonationsTotals.
This works great except that the resulting column chart appears to be
plotting cells (at the far right) that don't have any data in them.
Makes for an odd looking chart,

Can anyone suggest a solution to this problem? (In terms a rookie
like me could understand). We would be ever so grateful.
Thanks very much for your time!!!
S

 

[Jon Peltier]

COUNTA counts any cells with formulas, even if the formulas return "" to the
cell.

Also, if there are more categories than values, the chart will still have
room for all categories.

- Jon

 

[SJ]

COUNTA counts any cells with formulas, even if the formulas return "" to the
cell.

Also, if there are more categories than values, the chart will still have
room for all categories.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______








- Show quoted text -

Hi Jon. Thanks for taking the time to help.
I changed the COUNTA to COUNT and I got an error saying a formula was
making an invalid reference. So I changed it back.
The worksheet cells that hold the data that we have extracted from
Access do not contain any formulas so I don't see how that applies.
Also, there are never more categories than values because in the
initial query in Access, the categories have to have a value in order
to be part of the result. Those with corresponding values are the
only records it extracts.
Chances are good that I'm misunderstanding what you were trying to to
tell me, but I wasn't able to make it work.
Regardless, we really do appreciate your help.
S

 

[SJ]

Hi Jon.  Thanks for taking the time to help.
I changed the COUNTA to COUNT and I got an error saying a formula was
making an invalid reference.  So I changed it back.
The worksheet cells that hold the data that we have extracted from
Access do not contain any formulas so I don't see how that applies.
Also, there are never more categories than values because in the
initial query in Access, the categories have to have a value in order
to be part of the result.  Those with corresponding values are the
only records it extracts.
Chances are good that I'm misunderstanding what you were trying to to
tell me, but I wasn't able to make it work.
Regardless, we really do appreciate your help.
S- Hide quoted text -

- Show quoted text -

I think I found the solution. In the COUNTA portion of the Names
OFFSET function, I changed both the labels and data ranges to specific
cells
e.g.DonationsTotals=OFFSET(Donations!$K$2,0,0,COUNTA(Donations!$K$2:$K
$50),1).
This eliminated all of the empty points that were being plotted.
Seems to work.
Thanks!