CHARTING W/INDIRECT SERIES

[Ronbo]

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.

 

[John Mansfield]

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.

 

[Ronbo]

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.

Thanks, that works perfect.