How do I get 3 series in sync with the x-axis?

Nathan -

You can carry the pivot table approach further. When the pivot table source data is
changed, the dynamic range that defines the data changes. You could use a
WorkBook_Change routine that detects the change and updates the pivot table.

Create a defined name that grows with the pivot table data range (Sheet1!PT_Source).
right click the Sheet1 tab, View Source from the pop up menu. This opens the
worksheet code module in the VB Editor. Choose Worksheet from the top left dropdown,
which creates the Worksheet_Change event procedure in the module. Insert a few lines
that determine whether the defined pivot table source range has changes, and if so,
updates pivot tables in other sheets:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("PT_Source")) Is Nothing Then
Worksheets("Sheet2").PivotTables("PivotTable1").Pi votCache.Refresh
Worksheets("Sheet3").PivotTables("PivotTable1").Pi votCache.Refresh
Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh
End If
End Sub

This updates pivot tables on sheets 2, 3, and 4. Any defined names that determine
what piece of each pivot table to chart should update with the pivot tables. All the
user has to do is enable macros when the workbook first opens.

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


zizbird wrote:

My temporary solution before reading your latest post was to use a slew of
named ranges with inelegant names like 2004eoq3c2 and to add a workbook sheet
whose sole purpose was gathering totals/averages based on the actual data
sheets.

I added rows marked "Do not edit" to contain the named ranges so that the
named ranges get moved properly when rows are inserted at the edges of the
ranges.

That sounds like your suggestion for "uncomplicated data arrangements".

Since my data needs to be sorted by date for one chart and sorted by another
item for a second chart, ranged names for both sort orders would gridlock the
data: Sorted one way, inserting a row would require the redefinition of all
named ranges for sorting the other way that happened to be down-page of the
inserted row.

Removing the simpler set of named ranges and its associated chart and
re-creating that chart each time it is needed removes the gridlock, but isn't
a permanent solution.

I had begun to look at pivot tables and charts as a possible permanent
solution but found that pivot tables appear to be static data summaries,
requiring their re-creation every time new data is entered.

Excel seems to have tried very hard to make pivot tables/charts easy and
user-friendly but not easy enough for my end users who will only be able to
enter rows of data -- nothing else. Fifteen seconds for you (and probably for
me once I figure it out) is certainly quick enough but my successors won't be
able to make use of this.

Using a "dynamic range to define the pivot table source data" and refreshing
sounds good... I think I can trust them to refresh rand run a macro... if I
write a tutorial.

I'll have to learn more about these things myself first -- I'll post again
when I get any results to speak of.

Thank you very much for the help and links.

-Nathan


"Jon Peltier" wrote:


To make a long story short...

Your efforts to make the data entry system comprehensible to the user is admirable.
Of course, there's no rule that says the data entered must be plotted as is. For
uncomplicated data arrangements, simple worksheet formulas or defined names can make
the data plottable.

The data that you show is perfectly suited to analysis by pivot table.

DATE DATA SERIES_IDENTIFIER
1/1/2004 1 S1
1/3/2004 3 S1
1/4/2004 5 S1
1/7/2004 7 S1
1/1/2004 22 S2
1/5/2004 44 S2
1/7/2004 66 S2

In fifteen seconds I turned the data above into the chart-ready table below:

Sum of DATA2 SERIES_IDENTIFIER
DATE S1 S2
1/1/2004 1 22
1/3/2004 3
1/4/2004 5
1/5/2004 44
1/7/2004 7 66

If you use a dynamic range to define the pivot table source data, refreshing the
table will capture the added data. A pivot chart will update automatically when the
pivot table is updated, but cannot render a time scale axis. A regular chart will
only update the number of points in a series using named ranges for series data, but
will not add series accordingly. So you should use a regular chart, and update it
with a macro after refreshing the pivot table.

Thanks to Debra Dalgleish, there is a section on my web site that can help you get
started with pivot tables:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Debra adds to this on her own site. Look under P in the index:

http://contextures.com/tiptech.html

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