Excel - How do I create a graph of numbers that are in the same cell
across sevaral worsheets in the same file? Each week I add a new
worksheet to the file, and I want the graph to update to include the
new worksheet, without having to change the fomula.  (I'll be applying
this to dozens of cells so I dont want to change the formula's each
week). Please can you provice the forumula that would work across a
three worksheet file. Say for example that the cell is always B4.

---

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

Hello Headsetsdotcom,

What version of Microsoft Excel are you using?
Do you have an example of the chart formula you are using to produce
the chart [right now]?

I have done a number of operations with mulitple sheets but what you
describe is a little different and I would like to understand your
example more fully before answering. It may be helpful to post the
steps used to create a sample spreadsheet or post a copy where it can
be downloaded.

  --Maniac

---

Clarification of Question by headsetsdotcom-ga on 28 Mar 2004 18:42 PST

Maniac, office excel 2003. 

Create a file.  On sheet 1 put "5" in cell b4.  on Sheet 2 put "10" in
cell b4.  on sheet 3 put "24" in cell b4.  Now create a new sheet, and
a formula that will result in the sum of the three B4's. (39 is the
answer). However when we create new sheets each week, it goes up with
the new entries in B4 on the new sheets.  Dont worry about the graph. 
I can add that, it's the auto adding of the new B4's each week that I
want to be taken care of.  If you have somewhere I can send a sample
spreadsheet to i'll do that.

Mike

Hello Mike (aka Headsetsdotcom),

OK. That is MUCH more understandable and the following should satisfy your needs.

If you are not familiar with 3-D references - I suggest a brief look
at the following information in Excel Help. In my copy of Excel, the
title of the help section is "About cell and range references". If you
cannot find that, try a help search for 3-D references. I would also
check "Guidelines for using 3-D references" for some additional
information. In particular, the latter desribes which formulas work
with 3-D references.

To use 3-D references effectively, I suggest the following sheet
organization in the workbook:
  First
  Data01
  Data02
  ...
  Data99 (or however many you have)
  Last
  Sums
  Charts

I would sugest leaving the sheets named First and Last empty of
information. In case you don't know how to rename sheets - double
click on the name in the tab at the bottom of the sheet and type in
the new name. The names of the sheets between First and Last don't
matter - if you have a naming system already - I suggest you use it.
You could take a copy of your existing workbook at use
  Insert -> Worksheet
at the first of your existing data sheets and at the sums sheet
(assuming it is already after all the data sheets) to do this step.

The formula for cell in Sums that refers to B4 in the other sheets would then be
  =sum(First:Last!$B$4)
Above, I used an absolute reference here but a relative reference such as
  =sum(First:Last!B4)
may be more convenient if you want to copy down / right the cell
formula and the layout of the Sums sheet is similar to that of the
data sheets. To explain the 3-D reference:
  First:Last
refers to the range of sheets,
  !B4 (or !$B$4)
is the cell within the range of sheets to be summed.

I sometimes use a mix of absolute and relative references and can
explain the differences more fully if needed. Use a clarification
request if you want that added to the answer.

At this point, you should have a sheet suitable for generating the
graphs. I suggest putting the graphs on the Sums sheet or on
additional sheets after the sheet named Last. When you get the new
workbook set up the way you want - be sure to save it.

When you add a sheet to the new workbook - simply move it to an
appropriate place between First and Last and the sums will adjust
automatically. To do this, you can either drag / drop the sheet name
at the bottom of the worksheet area or use
  Edit -> Move or Copy Sheet
and a dialog box comes up. Note that the prompt is to move the sheet
"before" the sheet selected, so you can simply scroll down to Last,
and then enter OK.

That should handle the basic operations. If this answer does not meet
all your needs, or if some part is unclear (or you want more
information on cell references), be sure to request a clarification.

I found the relevant information in Excel's on line help. Good search
phrases include:
  multiple worksheets
  3-d references
  range worksheets

You can also try to find additional information using Google -
however, most of the top hits relate to training or certification.
Adding
  Excel -specialist -training -proficient
and similar phrases to the above searches can find some additional
information. For example, I found
  http://www.mum.edu/helpdesk/tutorials/formulas.html
which has the steps needed to enter a 3-D reference using the mouse
(about 1/3rd down the page).

Good luck with your work.

  --Maniac

Yep. It works. Great explanations too, you anticpated the potential
questions.  thanks. I"ll shortly post another follow on question.
Thanks! :-)