[AccessD] Automatically expanding source data parameter

Several possible solutions:

A) Columns or rows that are part of a data series but are hidden will not be
charted. You can use this to create data series with multiple empty hidden
columns that you unhide as you add data. 
B)It is also possible to create dynamic ranges that automatically change as
you add rows or columns. See:
http://www.ozgrid.com/Excel/DynamicRanges.htm
C)Pivot charts are an elegant way to create charts with rolling time
periods. Like a pivot table the chart accesses a worksheet data table that
you add to over time. The chart has drop down boxes that allow you to select
which periods you want to display.
D)You can change and set the range using VBA as you suggested although I for
one am too lazy to go to all that trouble given the other possible
solutions.

HTH
Jim Hale

-----Original Message-----
From: tewald at comcast.net [mailto:tewald at comcast.net]
Sent: Tuesday, October 11, 2005 9:10 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Automatically expanding source data parameter


Whether this will require VBA, or I'm just about to reveal (more of) my
ignorance with regard to MSFT's built-in chart abilities, I don't know.
Once a week I update data in several spreadsheets.Certain totals (the same
in each case) are copied from each of the sheets to one sheet comprised of
totals only. The data from each sheet has 3 charts based on that sheet's
data. The charts are all time-oriented in that past data remains, while a
new column is added to the individual totals tables and is to be included in
that table's chart. Each of the initial sheets has a counterpart chart sheet
containing 3 charts. I go to each of the 3 charts in each of the chart
sheets, and expand the Source Data parameter.
Here are examples of the Source Data from one of the chart sheets for
today's data:
='Scorecard Data'!$A$2:$O$7
='Scorecard Data'!$A$2:$O$3,'Scorecard Data'!$A$5:$O$5,'Scorecard
Data'!$A$12:$O$14
='Scorecard Data'!$A$2:$O$2,'Scorecard Data'!$A$4:$O$4,'Scorecard
Data'!$A$9:$O$11
I had to go to each of these and change the "N" to an "O", as you see above.
This is no big deal, but I have several sheets similar to this one. You will
note that the data contained in the last two lines are not contiguous, which
adds to the confusion. I would think naming a range, and then simply
redefining the range programmatically, would be a reasonable idea, except
for that lack of contiguity.
Is there a way to update each of these 3 parameters on several chart sheets
automatically?
TIA,
Tom Ewald
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.