CHARTING W/INDIRECT SERIES

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a data series to chart, say A1..A20. As I input new data I insert a
cell in A1 and enter the data. However on my chart source data it changes to
a new series of source data to B1..B21 when I insert "Shift Down" the cells
and insert new data. I want the data series to remain A1..A20. I have tried
(INDIRECT and OFFSET) but I get" Function not Valid"

Any ideas would be greatly appreciated.
 
I have not understood. if you enter new data what happens to the old data.
i visualise something like this
you have some data in col A. you create the chart
now highlight column A and click insert row.
the old data shifts to column B(column A is blank) and the corresponding
chart automatically refers to data in col. B
now you enter new data in column A and create chart.
repeat this process.

is this what your want
 
Ronbo,

Try going through the following to set up your chart:

Assume the data labels are in the range A1:A20, the data is in the range
B1:B20, the sheet in which the embedded chart resides is called “Sheet1â€, and
that the name of the workbook is “Workbook.xlsâ€.

Go to Insert -> Name -> Define and enter the following formula. Name the
formula “TestAâ€.

=INDIRECT("Sheet1!A1:A20")

Then, go to Insert -> Name -> Define and enter the following formula. Name
this formula “TestBâ€.

=INDIRECT("Sheet1!B1:B20")

Now, click on your chart and go to Chart -> Source Data

For the Series 1 Values, enter this reference

=Workbook.xls!TestB

For the Category (X) Axis Labels, enter this reference

=Workbook.xls!TestA

The chart should now be set up using defined names with "frozen" references
via the Indirect function.
 
John Mansfield said:
Ronbo,

Try going through the following to set up your chart:

Assume the data labels are in the range A1:A20, the data is in the range
B1:B20, the sheet in which the embedded chart resides is called “Sheet1â€, and
that the name of the workbook is “Workbook.xlsâ€.

Go to Insert -> Name -> Define and enter the following formula. Name the
formula “TestAâ€.

=INDIRECT("Sheet1!A1:A20")

Then, go to Insert -> Name -> Define and enter the following formula. Name
this formula “TestBâ€.

=INDIRECT("Sheet1!B1:B20")

Now, click on your chart and go to Chart -> Source Data

For the Series 1 Values, enter this reference

=Workbook.xls!TestB

For the Category (X) Axis Labels, enter this reference

=Workbook.xls!TestA

The chart should now be set up using defined names with "frozen" references
via the Indirect function.
Click to expand...



Thanks, that works perfect.
 
Back
Top