Home |
Search |
Today's Posts |
#1
|
|||
|
|||
CHARTING W/INDIRECT SERIES
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. |
#2
|
|||
|
|||
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 wrote in message ... 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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
"John Mansfield" wrote: 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. Thanks, that works perfect. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display multiple series in a pie chart? | Charts and Charting in Excel | |||
Plotting different length series in a chart | Charts and Charting in Excel | |||
graphing two series on x axis | Charts and Charting in Excel | |||
How do I force a series in a Bar-Line Chart to be a Bar? | Charts and Charting in Excel | |||
Format Data Series Markers | Charts and Charting in Excel |