Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ronbo
 
Posts: n/a
Default 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   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

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   Report Post  
John Mansfield
 
Posts: n/a
Default

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   Report Post  
Ronbo
 
Posts: n/a
Default



"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I display multiple series in a pie chart? AncientPC Charts and Charting in Excel 3 May 3rd 23 05:09 PM
Plotting different length series in a chart Big Red Charts and Charting in Excel 2 May 5th 05 02:00 PM
graphing two series on x axis Ladislav Ligart Charts and Charting in Excel 2 December 6th 04 07:50 PM
How do I force a series in a Bar-Line Chart to be a Bar? Excel Challenged Charts and Charting in Excel 1 December 6th 04 05:16 AM
Format Data Series Markers KB Charts and Charting in Excel 1 November 30th 04 04:37 AM


All times are GMT +1. The time now is 10:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"