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:759A2CA5-B000-452E-AD41-(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:F322D7FB-200A-42AB-AFE1-(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
Similar Threads
