Automatically Updating Charts for Additional Data

  • Thread starter Thread starter Sri
  • Start date Start date
S

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
 
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
 
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.
Click to expand...
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
Click to expand...
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.
 
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
Click to expand...

--

Cheers
Andy

http://www.andypope.info
 
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.
Click to expand...
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.
 
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
Click to expand...
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?
 
Back
Top