Thread: Graphing Variable-length columns

Hi, I have a situation where I have a spreadsheet with 2 columns, A and B.
Column A is the date and Column B is the # of bids we have submitted on that
day.

I create a graph based on those columns, that shows the date on the X-axis
and the # on the Y-axis.

But I would like the graph to be updated automatically everytime I add a new
row to the spreadsheet. So that tomorrow, when I add the row for May 10, the
chart will reflect May 10 without requiring me to recreate the chart.

Is this possible? I've tried everything I can think of and it hasn't worked.

Thanks!
Ben
merc1286@hotmail.com

Ben, I copy an answer to a similar post below:
You can set up your actual data as a dynamic named range series, using the
OFFSET function. Many MVPs give tutorials how to do this on their websites,
see for instance:
http://www.tushar-mehta.com/excel/ne...rts/index.html
http://www.stfx.ca/people/bliengme/e...ps.htm#Dynamic
http://peltiertech.com/Excel/Charts/Dynamics.html
http://www.contextures.com/xlNames02.html

GL,
Henk

"Ben M" wrote:

> Hi, I have a situation where I have a spreadsheet with 2 columns, A and B.
> Column A is the date and Column B is the # of bids we have submitted on that
> day.
>
> I create a graph based on those columns, that shows the date on the X-axis
> and the # on the Y-axis.
>
> But I would like the graph to be updated automatically everytime I add a new
> row to the spreadsheet. So that tomorrow, when I add the row for May 10, the
> chart will reflect May 10 without requiring me to recreate the chart.
>
> Is this possible? I've tried everything I can think of and it hasn't worked.
>
> Thanks!
> Ben
> merc1286@hotmail.com

"Ben M" <Ben M@discussions.microsoft.com> wrote in message
news:B1B45BBC-5544-44BA-B3F6-D2CA65744249@microsoft.com...
> Hi, I have a situation where I have a spreadsheet with 2 columns, A and B.
> Column A is the date and Column B is the # of bids we have submitted on
> that
> day.
>
> I create a graph based on those columns, that shows the date on the X-axis
> and the # on the Y-axis.
>
> But I would like the graph to be updated automatically everytime I add a
> new
> row to the spreadsheet. So that tomorrow, when I add the row for May 10,
> the
> chart will reflect May 10 without requiring me to recreate the chart.
>
> Is this possible? I've tried everything I can think of and it hasn't
> worked.

If your source data says something like =Sheet1!$A$1:$A$3 change it to
=Sheet1!$A:$A

If you add a new row you may get odd warnings about exceeding 32000 points,
but it still seems to work.
--
David Biddulph

That works fine, if you don't mind a series with 32k points (which I just
got when I tried it). Perhaps it depends on chart type.

The "official" answer is to define a dynamic range name that sizes itself,
and base the chart on that name. Here are some examples and links:

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

The good news, if you're using Excel 2003, is that you can define the chart
source data range as a "List", and when data is added to the list,
everything that relates to the list updates. Select the range and go to List
on the Data menu to create it. Then make a chart the regular way, and even
if it uses the normal cell address for the source data, when the address
changes due to the list changing size, the address in the chart also
changes.

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

"David Biddulph" <david@biddulph.org.uk> wrote in message
news:4461c79e$1_1@glkas0286.greenlnk.net...
> "Ben M" <Ben M@discussions.microsoft.com> wrote in message
> news:B1B45BBC-5544-44BA-B3F6-D2CA65744249@microsoft.com...
>> Hi, I have a situation where I have a spreadsheet with 2 columns, A and
>> B.
>> Column A is the date and Column B is the # of bids we have submitted on
>> that
>> day.
>>
>> I create a graph based on those columns, that shows the date on the
>> X-axis
>> and the # on the Y-axis.
>>
>> But I would like the graph to be updated automatically everytime I add a
>> new
>> row to the spreadsheet. So that tomorrow, when I add the row for May 10,
>> the
>> chart will reflect May 10 without requiring me to recreate the chart.
>>
>> Is this possible? I've tried everything I can think of and it hasn't
>> worked.
>
> If your source data says something like =Sheet1!$A$1:$A$3 change it to
> =Sheet1!$A:$A
>
> If you add a new row you may get odd warnings about exceeding 32000
> points, but it still seems to work.
> --
> David Biddulph
>