Automatically Updating Charts for Additional Data

[Sri]

Hello,

I have a data table to which I add sales volume on a daily
basis. I then create a chart based on all the volumes in
the data table. The only thing different from one day's
chart to the next is the addition of the new day's sales
volume. Is there a way I can cause the existing chart to
automatically update whenever I add a new sales volume
rather than needing to manually create the chart each day?

This data table is used for all of our branches - one
sheet per branch. Likewise we have 20 sheets for 20
branches, wherein we maintain the daily sales volume of
all the branches including the charts.

I thought of using a macro to automatically update the
chart's data range by one more column to reflect the newly
added data. But this also creates problem as the data
table is not organised in a similar way in all the sheets
(columns nos. are different).

I would be very happy if somebody gives me a best possible
solution.

Thanks,
Sri

 

[Guest]

Sri,
If you try to chart a #N/A error excel will ignore it. You could set up a hidden table that returns #N/A ( NA function) if the sale numbers are blank. Then chart your graph off of this hidden table. Note that the date and value both need to be #N/A in order to keep the x axis from showing all dates.

If you want to chart of the existing table you could use Conditional Formatting to change the font to white if the cell contains #N/A. This way the #N/A will not show up in the chart, and the white font will prevent it from being visible in the table.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Sri wrote: -----

Hello,

I have a data table to which I add sales volume on a daily
basis. I then create a chart based on all the volumes in
the data table. The only thing different from one day's
chart to the next is the addition of the new day's sales
volume. Is there a way I can cause the existing chart to
automatically update whenever I add a new sales volume
rather than needing to manually create the chart each day?

This data table is used for all of our branches - one
sheet per branch. Likewise we have 20 sheets for 20
branches, wherein we maintain the daily sales volume of
all the branches including the charts.

I thought of using a macro to automatically update the
chart's data range by one more column to reflect the newly
added data. But this also creates problem as the data
table is not organised in a similar way in all the sheets
(columns nos. are different).

I would be very happy if somebody gives me a best possible
solution.

Thanks,
Sri

 

[Sri]

Mark,

Thanks for your reply. But My question was totally
different from what you have answered.

My question was to automatically update the charts daily
instead of doing it manually.

I would be very greatful if you can help me in this.

Thanks
Sri

-----Original Message-----
Sri,
If you try to chart a #N/A error excel will ignore it.

You could set up a hidden table that returns #N/A ( NA
function) if the sale numbers are blank. Then chart your
graph off of this hidden table. Note that the date and
value both need to be #N/A in order to keep the x axis
from showing all dates.

If you want to chart of the existing table you could use

Conditional Formatting to change the font to white if the
cell contains #N/A. This way the #N/A will not show up in
the chart, and the white font will prevent it from being
visible in the table.

 

[Andy Pope]

Hi Sri,

The way to do this is to use dynamic named ranges.

Take a look at the examples on Jon's site. Maybe you adapt them to suit.
(http://peltiertech.com/Excel/Charts/Dynamics.html)

or Tushar's tutorial on Dynamic Charts.
(http://www.tushar-mehta.com/)

Hello,

I have a data table to which I add sales volume on a daily
basis. I then create a chart based on all the volumes in
the data table. The only thing different from one day's
chart to the next is the addition of the new day's sales
volume. Is there a way I can cause the existing chart to
automatically update whenever I add a new sales volume
rather than needing to manually create the chart each day?

This data table is used for all of our branches - one
sheet per branch. Likewise we have 20 sheets for 20
branches, wherein we maintain the daily sales volume of
all the branches including the charts.

I thought of using a macro to automatically update the
chart's data range by one more column to reflect the newly
added data. But this also creates problem as the data
table is not organised in a similar way in all the sheets
(columns nos. are different).

I would be very happy if somebody gives me a best possible
solution.

Thanks,
Sri

--

Cheers
Andy

http://www.andypope.info

 

[Guest]

Sri,
The techniques I suggested would update the chart automatically when you entered the new data into your table. Maybe I don't understand what you are charting. What type of chart? What are the X and Y axis lables?

Did Andy's references help you at all?

Regards,
Mark Graesser
(e-mail address removed)

----- Sri wrote: -----

Mark,

Thanks for your reply. But My question was totally
different from what you have answered.

My question was to automatically update the charts daily
instead of doing it manually.

I would be very greatful if you can help me in this.

Thanks
Sri

-----Original Message-----
Sri,
If you try to chart a #N/A error excel will ignore it.

You could set up a hidden table that returns #N/A ( NA
function) if the sale numbers are blank. Then chart your
graph off of this hidden table. Note that the date and
value both need to be #N/A in order to keep the x axis
from showing all dates.Conditional Formatting to change the font to white if the
cell contains #N/A. This way the #N/A will not show up in
the chart, and the white font will prevent it from being
visible in the table.

 

[Sri]

Thanks, May be I didn't understand ur suggestion.

yes Andy's reference was really helpful..

Thanks

-----Original Message-----
Sri,
The techniques I suggested would update the chart

automatically when you entered the new data into your
table. Maybe I don't understand what you are charting.
What type of chart? What are the X and Y axis lables?