(Msg. 1) Posted: Wed Aug 06, 2008 9:47 am
Post subject: Dynamic Chart (OFFSET Function) plots empty cells. Add to elertz Archived from groups: microsoft>public>excel>charting (more info?)
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
(Msg. 2) Posted: Wed Aug 06, 2008 6:03 pm
Post subject: Re: Dynamic Chart (OFFSET Function) plots empty cells. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
_______
"SJ" <SimonJester RemoveThis @moose-mail.com> wrote in message
news:fc19e092-cdf6-4952-9f14-9d794251e77e@d1g2000hsg.googlegroups.com...
>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
>
(Msg. 3) Posted: Thu Aug 07, 2008 9:08 am
Post subject: Re: Dynamic Chart (OFFSET Function) plots empty cells. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Aug 6, 3:03 pm, "Jon Peltier" <jonxlmv....DeleteThis@SPAMpeltiertech.com>
wrote:
> 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
> _______
>
> "SJ" <SimonJes....DeleteThis@moose-mail.com> wrote in message
>
> news:fc19e092-cdf6-4952-9f14-9d794251e77e@d1g2000hsg.googlegroups.com...
>
>
>
> >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- Hide quoted text -
>
> - 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
(Msg. 4) Posted: Thu Aug 07, 2008 10:14 am
Post subject: Re: Dynamic Chart (OFFSET Function) plots empty cells. Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Aug 7, 9:08 am, SJ <SimonJes....TakeThisOut@moose-mail.com> wrote:
> On Aug 6, 3:03 pm, "Jon Peltier" <jonxlmv....TakeThisOut@SPAMpeltiertech.com>
> wrote:
>
>
>
>
>
> > 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
> > _______
>
> > "SJ" <SimonJes....TakeThisOut@moose-mail.com> wrote in message
>
> >news:fc19e092-cdf6-4952-9f14-9d794251e77e@d1g2000hsg.googlegroups.com...
>
> > >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- Hide quoted text -
>
> > - 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- 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!
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office