data source remain constant

#1 10-27-2005, 01:05 PM

I have line chart that uses values in column C. Periodically I insert a new
column of data which then becomes the new column C. Now the chart is
displaying the data in column D but would like it to always chart the values
in C. I tried removing the absolute references in the source data but it
won't allow that. Other suggestions?
--
maryj

#2 10-28-2005, 09:05 AM

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/
_______

maryj wrote:
> I have line chart that uses values in column C. Periodically I insert a new
> column of data which then becomes the new column C. Now the chart is
> displaying the data in column D but would like it to always chart the values
> in C. I tried removing the absolute references in the source data but it
> won't allow that. Other suggestions?

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)