Hello,
I assume that there's an easy way to do this, and I just haven't found it
yet. I have a table with the headers in row 2, and data beneath. When new
data is added to the table, it is inserted in row 3, and the rest of the data
is pushed down one row. Unfortunately, when this happens, the chart "splits"
the source data range to only include the data that was there prior. Here's
an example:

Say my data table is in A2:B10, with the headers in row 2. The equation in
the "Source Data" field is

=SheetName!A2:B10

When new data is added, the data table becomes A2:B11, and the equation in
the source data field becomes

=Sheetname!A2:B2,Sheetname!A4:B11

What I'd like is for, after the new data is added, the equation to become

=Sheetname!A2:B11

I've tried anchoring parts of the table with "$", naming the range and I've
tried using the Indirect function, to no avail. The indirect function
example would be:

=Indirect("Sheetname!A2"):Sheetname!B10

This charts fine, but as soon as new data is added, Excel removes the
Indirect function and reverts to the split reference above. Same thing
happened when I used a named range. I could write a fairly simple VBA
routine that would update the source data, but I'd rather see if there's a
way to have the application do it automatically, if possible.

If anyone has any ideas, I'd certainly appreciate it.

Excel2000, WinXPPro, Chart is on a separate sheet, if that matters.

Thanks to all.

Mike

Check out the dynamic chart examples and links on this page:

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

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

mikelee101 wrote:

Hello,
I assume that there's an easy way to do this, and I just haven't found it
yet. I have a table with the headers in row 2, and data beneath. When new
data is added to the table, it is inserted in row 3, and the rest of the data
is pushed down one row. Unfortunately, when this happens, the chart "splits"
the source data range to only include the data that was there prior. Here's
an example:

Say my data table is in A2:B10, with the headers in row 2. The equation in
the "Source Data" field is

=SheetName!A2:B10

When new data is added, the data table becomes A2:B11, and the equation in
the source data field becomes

=Sheetname!A2:B2,Sheetname!A4:B11

What I'd like is for, after the new data is added, the equation to become

=Sheetname!A2:B11

I've tried anchoring parts of the table with "$", naming the range and I've
tried using the Indirect function, to no avail. The indirect function
example would be:

=Indirect("Sheetname!A2"):Sheetname!B10

This charts fine, but as soon as new data is added, Excel removes the
Indirect function and reverts to the split reference above. Same thing
happened when I used a named range. I could write a fairly simple VBA
routine that would update the source data, but I'd rather see if there's a
way to have the application do it automatically, if possible.

If anyone has any ideas, I'd certainly appreciate it.

Excel2000, WinXPPro, Chart is on a separate sheet, if that matters.

Thanks to all.

Mike

Jon,
This looks like it'll do the trick. I'll get a chance to read it in more
detail and try it out tonight.

Thanks a million for the help.

Mike

"Jon Peltier" wrote:

Check out the dynamic chart examples and links on this page:

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

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

mikelee101 wrote:

Hello,
I assume that there's an easy way to do this, and I just haven't found it
yet. I have a table with the headers in row 2, and data beneath. When new
data is added to the table, it is inserted in row 3, and the rest of the data
is pushed down one row. Unfortunately, when this happens, the chart "splits"
the source data range to only include the data that was there prior. Here's
an example:

Say my data table is in A2:B10, with the headers in row 2. The equation in
the "Source Data" field is

=SheetName!A2:B10

When new data is added, the data table becomes A2:B11, and the equation in
the source data field becomes

=Sheetname!A2:B2,Sheetname!A4:B11

What I'd like is for, after the new data is added, the equation to become

=Sheetname!A2:B11

I've tried anchoring parts of the table with "$", naming the range and I've
tried using the Indirect function, to no avail. The indirect function
example would be:

=Indirect("Sheetname!A2"):Sheetname!B10

This charts fine, but as soon as new data is added, Excel removes the
Indirect function and reverts to the split reference above. Same thing
happened when I used a named range. I could write a fairly simple VBA
routine that would update the source data, but I'd rather see if there's a
way to have the application do it automatically, if possible.

If anyone has any ideas, I'd certainly appreciate it.

Excel2000, WinXPPro, Chart is on a separate sheet, if that matters.

Thanks to all.

Mike