Thread: [SOLVED] Chart that will expand to aditional rows

I have a chart now with this in the data range:
=PHQChartQuery!$A$2:$C$7

But I would like it to look like this
=PHQChartQuery!$A$2:$C$500

But when I do there there alot of extra points on the graph. I would like
it to only show on the graph only the rows that have data in them and then if
I add data later on it will update the graph. So I don't have to keep
redoing the range everytime data is entered.

Is this possible?

Hi,

You need to use named ranges with dynamic formula.
Have a look at Jon Peltier's page, which contains information on the
technique and links to other sites.

http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

pokdbz wrote:
> I have a chart now with this in the data range:
> =PHQChartQuery!$A$2:$C$7
>
> But I would like it to look like this
> =PHQChartQuery!$A$2:$C$500
>
> But when I do there there alot of extra points on the graph. I would like
> it to only show on the graph only the rows that have data in them and then if
> I add data later on it will update the graph. So I don't have to keep
> redoing the range everytime data is entered.
>
> Is this possible?
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Yes, make a so-called "dynamic named range" with OFFSET, see for instance
http://www.cpearson.com/excel/named.htm
http://www.contextures.com/xlNames01.html#Dynamic
GL,
Henk

"pokdbz" wrote:

> I have a chart now with this in the data range:
> =PHQChartQuery!$A$2:$C$7
>
> But I would like it to look like this
> =PHQChartQuery!$A$2:$C$500
>
> But when I do there there alot of extra points on the graph. I would like
> it to only show on the graph only the rows that have data in them and then if
> I add data later on it will update the graph. So I don't have to keep
> redoing the range everytime data is entered.
>
> Is this possible?
>
>

I'm not quite sure what to do with the Refers to: for the offset

Here is what my data looks like in the excel sheet
SSN PHQDate PHQTOT
111111111 5/23/2006 13
111111111 5/24/2006 14
111111111 5/25/2006 12
111111111 5/26/2006 13
111111111 6/2/2006 13

My sheet is called PHQChartQuery
and the file is called PHQChart

Would you be able to help me out edit the offset for my data?
"HEK" wrote:

> Yes, make a so-called "dynamic named range" with OFFSET, see for instance
> http://www.cpearson.com/excel/named.htm
> http://www.contextures.com/xlNames01.html#Dynamic
> GL,
> Henk
>
> "pokdbz" wrote:
>
> > I have a chart now with this in the data range:
> > =PHQChartQuery!$A$2:$C$7
> >
> > But I would like it to look like this
> > =PHQChartQuery!$A$2:$C$500
> >
> > But when I do there there alot of extra points on the graph. I would like
> > it to only show on the graph only the rows that have data in them and then if
> > I add data later on it will update the graph. So I don't have to keep
> > redoing the range everytime data is entered.
> >
> > Is this possible?
> >
> >