+ Reply to Thread
Results 1 to 4 of 4

charting w/indirect series

  1. #1
    Ronbo
    Guest

    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. #2
    R.VENKATARAMAN
    Guest

    Re: CHARTING W/INDIRECT SERIES

    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 <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    John Mansfield
    Guest

    RE: CHARTING W/INDIRECT SERIES

    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. #4
    Ronbo
    Guest

    RE: CHARTING W/INDIRECT SERIES



    "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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1