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.
----
Regards,
John Mansfield
http://www.pdbook.com
"Ronbo" wrote:
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.