PC Review


Reply
Thread Tools Rate Thread

How can I update multiple excel charts at the same time?

 
 
emsy85
Guest
Posts: n/a
 
      5th Jun 2008
Hi. I have about a zillion charts to update on a daily basis in the same
worksheet. I wondered if there was a way to update all the charts at once?
Thanks
 
Reply With Quote
 
 
 
 
David Biddulph
Guest
Posts: n/a
 
      5th Jun 2008
The charts are presumably based on data in one or more worksheets, so to
update your charts you need to update the worksheets which include the data.
I'm sorry if you find that the answer is as unclear as the question was.
--
David Biddulph

"emsy85" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi. I have about a zillion charts to update on a daily basis in the same
> worksheet. I wondered if there was a way to update all the charts at once?
> Thanks



 
Reply With Quote
 
 
 
 
emsy85
Guest
Posts: n/a
 
      5th Jun 2008
Thanks for the response. The charts are feeding from various spreadsheets
which are updated daily with large amounts of data. At the moment I manually
update each chart and extend the data source by one day each day. I wondered
if there was a way of updating all the charts at the same time once the data
is updated.

"David Biddulph" wrote:

> The charts are presumably based on data in one or more worksheets, so to
> update your charts you need to update the worksheets which include the data.
> I'm sorry if you find that the answer is as unclear as the question was.
> --
> David Biddulph
>
> "emsy85" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi. I have about a zillion charts to update on a daily basis in the same
> > worksheet. I wondered if there was a way to update all the charts at once?
> > Thanks

>
>
>

 
Reply With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      5th Jun 2008
There are a few options.

1. If you're running Excel 2003, define the source data as a List (data
menu). In Excel 2007, Lists were renamed Tables. In both cases, any
formulas, including chart series formulas, which reference the entire column
of a List or Table will update as the list or table adds or removes rows.

2. If you can't do #1, make dynamic charts which have dynamic ranges
defining their data regions. This is covered here:

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

3. If the changes are simple, and the sheet names are not too intricate, you
could try the (free) series formula editor on my web site:

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

This is particularly useful if all you have to change is, for example, the
last row number in the source data. It does the active chart or any number
of charts at a time.

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


"emsy85" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks for the response. The charts are feeding from various spreadsheets
> which are updated daily with large amounts of data. At the moment I
> manually
> update each chart and extend the data source by one day each day. I
> wondered
> if there was a way of updating all the charts at the same time once the
> data
> is updated.
>
> "David Biddulph" wrote:
>
>> The charts are presumably based on data in one or more worksheets, so to
>> update your charts you need to update the worksheets which include the
>> data.
>> I'm sorry if you find that the answer is as unclear as the question was.
>> --
>> David Biddulph
>>
>> "emsy85" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Hi. I have about a zillion charts to update on a daily basis in the
>> > same
>> > worksheet. I wondered if there was a way to update all the charts at
>> > once?
>> > Thanks

>>
>>
>>



 
Reply With Quote
 
emsy85
Guest
Posts: n/a
 
      5th Jun 2008
Jon - Thanks so much. I already found some info on your website which has
helped.

"Jon Peltier" wrote:

> There are a few options.
>
> 1. If you're running Excel 2003, define the source data as a List (data
> menu). In Excel 2007, Lists were renamed Tables. In both cases, any
> formulas, including chart series formulas, which reference the entire column
> of a List or Table will update as the list or table adds or removes rows.
>
> 2. If you can't do #1, make dynamic charts which have dynamic ranges
> defining their data regions. This is covered here:
>
> http://peltiertech.com/Excel/Charts/...umnChart1.html
> http://peltiertech.com/Excel/Charts/Dynamics.html
>
> 3. If the changes are simple, and the sheet names are not too intricate, you
> could try the (free) series formula editor on my web site:
>
> http://peltiertech.com/Excel/Charts/ChgSrsFmla.html
>
> This is particularly useful if all you have to change is, for example, the
> last row number in the source data. It does the active chart or any number
> of charts at a time.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "emsy85" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Thanks for the response. The charts are feeding from various spreadsheets
> > which are updated daily with large amounts of data. At the moment I
> > manually
> > update each chart and extend the data source by one day each day. I
> > wondered
> > if there was a way of updating all the charts at the same time once the
> > data
> > is updated.
> >
> > "David Biddulph" wrote:
> >
> >> The charts are presumably based on data in one or more worksheets, so to
> >> update your charts you need to update the worksheets which include the
> >> data.
> >> I'm sorry if you find that the answer is as unclear as the question was.
> >> --
> >> David Biddulph
> >>
> >> "emsy85" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Hi. I have about a zillion charts to update on a daily basis in the
> >> > same
> >> > worksheet. I wondered if there was a way to update all the charts at
> >> > once?
> >> > Thanks
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
Richard Valle
Guest
Posts: n/a
 
      4th Jan 2011
I saw your post from 2008 about wanting to easily update multiple excel charts or graphs without having to change the SourceData for each graph every day or week (however frequent the time-scale happens to be).

If this subject is still of any slightest interest, please let me know.

I use scores of 12-week graphs that must update every week (adding the new figures each week and discarding the figures from 13 weeks ago). Manually, this could be VERY time consuming.

I just conceived, tested and now use a tamper-proof and simple solution that works beautifully and automatically. I just implemented this in my own graphs and pie charts, et al. The charts update as soon as the most current figures are entered.

It is a comparatively simple and is a clever use of embedding the functions "VLOOKUP()" and "NOW()".

Because it uses "NOW()", the right-hand side of the chart (if you want a line or bar chart) is always the most current data.

To do this, you create what I call a "mirror worksheet". This mirrors the worksheet containing that contains the SourceData. Because the mirror uses "NOW()" the results in the "mirror worksheet" are constantly updating (on their own) to the newest data on the SourceData sheet.

The "VLOOKUP()" function is used to create the mirror and "NOW()" is used to be constantly looking up the most current SourceData information based on your computer clock.

The beauty is that the chart's data source never has to change because the mirror constantly reflects only the most current information on its own, based on your computer clock.

Once this is done, the mirror sheet can be HIDDEN and never touched again. The graphs will update automatically from then on and once it is set up.

Exactly how to set this up can be figured out with the above information, but I will be doing a full write-up on how to do this with more specific directions later.

Let me know if there is interest and I will hurry up with the full write-up.

Richard

> On Thursday, June 05, 2008 9:15 AM emsy8 wrote:


> Hi. I have about a zillion charts to update on a daily basis in the same
> worksheet. I wondered if there was a way to update all the charts at once?
> Thanks



>> On Thursday, June 05, 2008 9:43 AM David Biddulph wrote:


>> The charts are presumably based on data in one or more worksheets, so to
>> update your charts you need to update the worksheets which include the data.
>> I'm sorry if you find that the answer is as unclear as the question was.
>> --
>> David Biddulph
>>
>> "emsy85" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...



>>> On Thursday, June 05, 2008 10:01 AM emsy8 wrote:


>>> Thanks for the response. The charts are feeding from various spreadsheets
>>> which are updated daily with large amounts of data. At the moment I manually
>>> update each chart and extend the data source by one day each day. I wondered
>>> if there was a way of updating all the charts at the same time once the data
>>> is updated.
>>>
>>> "David Biddulph" wrote:



>>>> On Thursday, June 05, 2008 10:33 AM Jon Peltier wrote:


>>>> There are a few options.
>>>>
>>>> 1. If you're running Excel 2003, define the source data as a List (data
>>>> menu). In Excel 2007, Lists were renamed Tables. In both cases, any
>>>> formulas, including chart series formulas, which reference the entire column
>>>> of a List or Table will update as the list or table adds or removes rows.
>>>>
>>>> 2. If you can't do #1, make dynamic charts which have dynamic ranges
>>>> defining their data regions. This is covered here:
>>>>
>>>> http://peltiertech.com/Excel/Charts/...umnChart1.html
>>>> http://peltiertech.com/Excel/Charts/Dynamics.html
>>>>
>>>> 3. If the changes are simple, and the sheet names are not too intricate, you
>>>> could try the (free) series formula editor on my web site:
>>>>
>>>> http://peltiertech.com/Excel/Charts/ChgSrsFmla.html
>>>>
>>>> This is particularly useful if all you have to change is, for example, the
>>>> last row number in the source data. It does the active chart or any number
>>>> of charts at a time.
>>>>
>>>> - Jon
>>>> -------
>>>> Jon Peltier, Microsoft Excel MVP
>>>> Tutorials and Custom Solutions
>>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>>> _______
>>>>
>>>>
>>>> "emsy85" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...



>>>>> On Thursday, June 05, 2008 10:47 AM emsy8 wrote:


>>>>> Jon - Thanks so much. I already found some info on your website which has
>>>>> helped.
>>>>>
>>>>> "Jon Peltier" wrote:



>>>>> Submitted via EggHeadCafe
>>>>> Microsoft SQL Server DBA For Beginners
>>>>> http://www.eggheadcafe.com/training-...erver-DBA.aspx

 
Reply With Quote
 
New Member
Join Date: Jun 2012
Posts: 1
 
      11th Jun 2012
Yes, Richard, I would be interested in your write up.

Joe K.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Bar Charts Issue - How Dynamic can charts be? Jon McKay Microsoft Excel Programming 0 12th Jul 2011 02:55 PM
Update same charts in multiple sheets at once Div Microsoft Excel Worksheet Functions 1 28th Jan 2009 09:10 PM
Convert linked Excel charts to embedded Excel charts in PPT 2007 Mohan Kumar Karunakaran Microsoft Powerpoint 2 26th Sep 2008 02:30 PM
link excel charts to web pages and update charts automatically Signguy Microsoft Excel Charting 1 22nd Apr 2008 08:29 PM
Charts - How to have multiple charts share a legend. =?Utf-8?B?U2Vhbg==?= Microsoft Excel Charting 2 20th Nov 2007 05:49 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:17 AM.