You need to make a dynamic chart, which uses named ranges for its series
data. I assume your chart uses row 1 for category labels, and rows below
that for values of the series in the chart.

Open the Define Names dialog (Insert menu > Names > Define, or CTRL+F3
shortcut). Enter a name in the Name box, something like XCategories, and
in the Refers To box, enter a formula like:

=OFFSET($B$1,0,1,1,COUNTA(OFFSET($B$1,0,1,1,255))

This refers to the range 0 rows below and one column to the right of B1,
which is one row high and COUNTA(blah) columns wide. Look up OFFSET for
more details on the function. The range is known to Excel by the name
you entered (XCategories).

There's your X values. Here's how to get the Y values. For each series
you have, define a named range like this:

Name: YValues1
Refers To: = OFFSET(XCategories,0,1)

Name: YValues2
Refers To: = OFFSET(XCategories,0,2)

These basically mean, move down 1, 2, etc., rows from the XCategories
range, and name it YValues1, 2, etc.

Now go to the chart. Select a series and look at the SERIES formula in
the Formula Bar:

=SERIES(Sheet1!$B$2,Sheet1!$C$1:$M$1,Sheet1!$C$2:$M$2,1)

This means the series name is in B2, the X values are in C1:M1, the Y
values are in C2:M2, and it's the first series in the chart. Change this
formula, using the names you defined:

=SERIES(Sheet1!$B$2,Sheet1!XCategories,Sheet1!YValues1,1)

Don't worry if the sheet name is replaced by the workbook name in front
of the names:

=SERIES(Sheet1!$B$2,Book1.xls!XCategories,Book1.xls!YValues1,1)

For more information, examples, and links, check out this web page:

  http://peltiertech.com/Excel/Charts/Dynamics.html

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