This is a follow on to my previous question.

I want to graph the contents of a static cell, B4, across multiple
sheets in a workbook, such that when I add a new sheet each week, the
graph updates. Please provide the means to do this on a sample
workbook with just B4 filled out on several sheets. The graph should
update when a new sheet is added that has a b4 cell filled in.

---

Request for Question Clarification by maniac-ga on 28 Mar 2004 20:35 PST

Hello again Mike,

It appears this is the question I thought you asked before where you
want to enter something like:
  =SERIES(,,First:Last!$B$4,1)
as the series formula of the chart and have Excel do the "right thing"
with the results (plot all B4 values from sheets First through Last).
If this is not the case, please clarify what you are trying to do.

This unfortunately is not something that works as-is in Excel. My
usual way of doing this is to have a separate sheet with references
like:
  =Data01!B4  =Data02!B4  ... =Data99!B4
in a row and plot that. You will have to ignore the errors when
entering the cell references. When the sheet does not exist, you get a
#REF! error, but the charting tool will leave those values as zero
when plotting the data. When you add the sheet (with the right name) -
it will then plot in series with the other values.

If that approach is not "good enough", I can build a macro that
handles the problem (assuming the organization of the workbook in your
previous question) by generating the series formulas directly. Let me
know if you want to pursue this kind of answer. The macro could work
in one or two ways - generate a new chart (on a worksheet or as a
separate sheet) or update an existing chart (replace an existing
series or add a new one). If you want to pursue the macro approach, it
would be sometime tomorrow before I can put something like that
together.

  --Maniac

---

Clarification of Question by headsetsdotcom-ga on 28 Mar 2004 20:55 PST

The Macro would be good, I have five cells I want to graph.  They are
E4, through E8.  Does this give you enough info? Would it help to send
you the spreadsheet?  Mike

Hello Mike,

The macro listed below assumes you have the following sheets in your workbook:
 - Charts - to get the data extracted from the data sheets and produce
a chart. It can be (should be) blank.
 - First - a blank sheet before the data sheets
 - one or more data sheets
 - Last - a blank sheet after the data sheets

The steps necessary to add the macro to your work sheet is as follows:

  Tools -> Macro -> Visual Basic Editor

At this point, you should get some additional windows and the visual
basic editor will be running. If you don't have any existing macros,
then
  Insert -> Module

The title of the window should be something like
  Workbook1 - Module1 (Code)
(replace Workbook1 with the name of your workbook)

Copy / paste the macro below into this window. If you want a more
descriptive name for the macro, change the first line which now reads
 Sub Headsetsdotcom()
to something more descriptive like
 Sub GenerateGraph()

  Excel -> Close and return to Microsoft Excel

This should bring up the spreadsheet again. You should only have to do
the steps up to this point once. The macro will be saved with the
workbook (and the data in the workbook).

The first time, or whenever you add one or more data sheets, run the macro using

  Tools -> Macro -> Macros

which brings up a dialog box. The macro below is named Headsetsdotcom
[unless you renamed it], select it and click the Run button. The macro
will automatically switch to the Charts worksheet, fill in the top
section with the extracted data from the data sheets and add a line
plot of the data.

If you have problems up to this point - don't hesitate to ask for
clarification on the steps, describing what steps worked and what did
not.

Now - the chart I produced might not be quite what you are looking
for. In the comments below (lines starting with '), I have included a
number of notes [numbers in square brackets] to describe how to
customize the macro. If you use

  Tools -> Macro -> Macros

then select the Headsetsdotcom macro and click on Edit, it should put
you back into the Visual Basic editor. Scroll down to the notes and
make changes as appropriate.

[1] Column headings currently are E4 through E8; I expect you want
some other names here.

[2] If you add another Cell to plot (e.g., E9); add a line similar to
the other ones.

[3] Modify the chart type. You can use
  View -> Object Browser
and enter xlcharttype as the search string (field at the top). The
lower right scrolling window should show the choices of chart types.
The names are pretty self explanatory but if you cannot find the one
you want - describe the type you want in a clarification request.

You might want to swap the rows / columns as well, use PlotBy := xlColumns instead.

[4] through [6] Update the chart, X-axis, and Y-axis titles as needed.

That should cover the customizations that I could think of. If this
does not quite meet your needs - please clarify them so I can update
the macro to better satisfy them.
  --Maniac


Sub Headsetsdotcom()
'
' Headsetsdotcom Macro
' Macro recorded 3/29/2004 by Maniac
'

'
' Compute how many sheets we need to process
' While we are at it, copy the sheet names to the first column
'
    Sheets("Charts").Select
    FirstSheet = 0
    NumberSheets = 0
    For I = 1 To Sheets.Count
    If Sheets(I).Name = "First" Then
        FirstSheet = I
    End If
    If Sheets(I).Name = "Last" Then
        NumberSheets = I - FirstSheet - 1
        Exit For
    End If
    ' add 1 to row number to skip first row
    Cells(I - FirstSheet + 1, 1).Value = Sheets(I).Name
    Next I
    Cells(1, 1).Value = ""

'
' Add the column labels to the top of the sheet
' [1]
' Replace the values below with the X labels desired
' on the generated chart
'
    Range("B1").FormulaR1C1 = "E4"
    Range("C1").FormulaR1C1 = "E5"
    Range("D1").FormulaR1C1 = "E6"
    Range("E1").FormulaR1C1 = "E7"
    Range("F1").FormulaR1C1 = "E8"
    
'
' Fill the rows with data copied from the sheets
' I loops through the rows / number of sheets
' [2] If you add cells to plot, modify the loop below
'
    For I = 1 To NumberSheets
        S$ = "=" + Sheets(I + FirstSheet).Name + "!"
        Cells(I + 1, 2).Formula = S$ + "E4"
        Cells(I + 1, 3).Formula = S$ + "E5"
        Cells(I + 1, 4).Formula = S$ + "E6"
        Cells(I + 1, 5).Formula = S$ + "E7"
        Cells(I + 1, 6).Formula = S$ + "E8"
    Next I
    Charts.Add
    ' [3] Modify chart type here, this is lines w/ markers
    ' Look up ChartType in the VBA Object browser for possible values
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Charts").Range("A1:F4"), PlotBy:= _
        xlRows
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
    With ActiveChart
        .HasTitle = True
        ' [4] Update title as appropriate
        .ChartTitle.Characters.Text = "My Chart"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        ' [5] Update x axis title as appropriate
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cell Reference"
        .Axes(xlValue, xlPrimary).HasTitle = True
        ' [6] Update y axis title as appropriate
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"
    End With
    ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False
    
    Sheets("Charts").Cells(1, 1).Select

End Sub

I'll not go into mass detail on this as there is plenty of it in the
Excel help file on this subject anyway. Basically what you need to do
is use a template linked to a database worksheet. each time you start
a new week you begin with a clean template and the data is added to
the database. You then link your chart to the database. Excel handels
all this for you automatically. Hit F1 in excel and search for
"template".