+ Reply to Thread
Results 1 to 5 of 5

Chart values from several worksheets

  1. #1
    Mike
    Guest

    Chart values from several worksheets

    I have several worksheets that have a value located in the same cell on each
    worksheet. I would like to graph these values.

    Each worksheet is a monthly summary of activity for an event. Each
    worksheet includes simple average and sum calculations, and it is the value
    of the sum on each worksheet that I want to graph. To complicate this, I
    would liek to set it up so tht I can insert a new worksheet into the set and
    have it's number automatcially added to the graph.

    I've looked at ranges and it looks promising, but everythimg I try to define
    a graph data source I get an error that says something like "Reference is not
    valid"

    The data range I am attempting to use is: ='1:3'!$E$41

    Any suggestions or comments about what I am doing wrong?

    -Michael

  2. #2
    John Mansfield
    Guest

    RE: Chart values from several worksheets

    Mike,

    The chart series can refer to data in other worksheets or workbooks, but the
    data for each series must reside on a single sheet.

    I would suggest creating a summary range on a single sheet that contains
    formulas that refer to the other sheets or workbooks with data that you want
    to include in your chart. Then, create the chart based on the summary range.
    It will be much easier to maintain this way.

    ----
    Regards,
    John Mansfield
    http://www.pdbook.com

    "Mike" wrote:

    > I have several worksheets that have a value located in the same cell on each
    > worksheet. I would like to graph these values.
    >
    > Each worksheet is a monthly summary of activity for an event. Each
    > worksheet includes simple average and sum calculations, and it is the value
    > of the sum on each worksheet that I want to graph. To complicate this, I
    > would liek to set it up so tht I can insert a new worksheet into the set and
    > have it's number automatcially added to the graph.
    >
    > I've looked at ranges and it looks promising, but everythimg I try to define
    > a graph data source I get an error that says something like "Reference is not
    > valid"
    >
    > The data range I am attempting to use is: ='1:3'!$E$41
    >
    > Any suggestions or comments about what I am doing wrong?
    >
    > -Michael


  3. #3
    Mike
    Guest

    RE: Chart values from several worksheets

    Thanks for the reply, John.

    I kind of figured that would be the case...

    Is there a way to dynamically put the value of ONE cell, from each of the
    worksheets, into a columnar list on the summary sheet? Would this be done
    via consolidate? Array?

    -Michael

    "John Mansfield" wrote:

    > Mike,
    >
    > The chart series can refer to data in other worksheets or workbooks, but the
    > data for each series must reside on a single sheet.
    >
    > I would suggest creating a summary range on a single sheet that contains
    > formulas that refer to the other sheets or workbooks with data that you want
    > to include in your chart. Then, create the chart based on the summary range.
    > It will be much easier to maintain this way.
    >
    > ----
    > Regards,
    > John Mansfield
    > http://www.pdbook.com
    >
    > "Mike" wrote:
    >
    > > I have several worksheets that have a value located in the same cell on each
    > > worksheet. I would like to graph these values.
    > >
    > > Each worksheet is a monthly summary of activity for an event. Each
    > > worksheet includes simple average and sum calculations, and it is the value
    > > of the sum on each worksheet that I want to graph. To complicate this, I
    > > would liek to set it up so tht I can insert a new worksheet into the set and
    > > have it's number automatcially added to the graph.
    > >
    > > I've looked at ranges and it looks promising, but everythimg I try to define
    > > a graph data source I get an error that says something like "Reference is not
    > > valid"
    > >
    > > The data range I am attempting to use is: ='1:3'!$E$41
    > >
    > > Any suggestions or comments about what I am doing wrong?
    > >
    > > -Michael


  4. #4
    Jon Peltier
    Guest

    Re: Chart values from several worksheets

    Mike -

    I have posted a method here:

    http://peltiertech.com/Excel/ChartsH...iffSheets.html

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    Mike wrote:

    > Thanks for the reply, John.
    >
    > I kind of figured that would be the case...
    >
    > Is there a way to dynamically put the value of ONE cell, from each of the
    > worksheets, into a columnar list on the summary sheet? Would this be done
    > via consolidate? Array?
    >
    > -Michael
    >
    > "John Mansfield" wrote:
    >
    >
    >>Mike,
    >>
    >>The chart series can refer to data in other worksheets or workbooks, but the
    >>data for each series must reside on a single sheet.
    >>
    >>I would suggest creating a summary range on a single sheet that contains
    >>formulas that refer to the other sheets or workbooks with data that you want
    >>to include in your chart. Then, create the chart based on the summary range.
    >> It will be much easier to maintain this way.
    >>
    >>----
    >>Regards,
    >>John Mansfield
    >>http://www.pdbook.com
    >>
    >>"Mike" wrote:
    >>
    >>
    >>>I have several worksheets that have a value located in the same cell on each
    >>>worksheet. I would like to graph these values.
    >>>
    >>>Each worksheet is a monthly summary of activity for an event. Each
    >>>worksheet includes simple average and sum calculations, and it is the value
    >>>of the sum on each worksheet that I want to graph. To complicate this, I
    >>>would liek to set it up so tht I can insert a new worksheet into the set and
    >>>have it's number automatcially added to the graph.
    >>>
    >>>I've looked at ranges and it looks promising, but everythimg I try to define
    >>>a graph data source I get an error that says something like "Reference is not
    >>>valid"
    >>>
    >>>The data range I am attempting to use is: ='1:3'!$E$41
    >>>
    >>>Any suggestions or comments about what I am doing wrong?
    >>>
    >>>-Michael


  5. #5
    Mike
    Guest

    Re: Chart values from several worksheets

    Thank you, Thank you, Thank you!

    This is a WONDERFUL place for advice!

    -Michael

    "Jon Peltier" wrote:

    > Mike -
    >
    > I have posted a method here:
    >
    > http://peltiertech.com/Excel/ChartsH...iffSheets.html
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    > Mike wrote:
    >
    > > Thanks for the reply, John.
    > >
    > > I kind of figured that would be the case...
    > >
    > > Is there a way to dynamically put the value of ONE cell, from each of the
    > > worksheets, into a columnar list on the summary sheet? Would this be done
    > > via consolidate? Array?
    > >
    > > -Michael
    > >
    > > "John Mansfield" wrote:
    > >
    > >
    > >>Mike,
    > >>
    > >>The chart series can refer to data in other worksheets or workbooks, but the
    > >>data for each series must reside on a single sheet.
    > >>
    > >>I would suggest creating a summary range on a single sheet that contains
    > >>formulas that refer to the other sheets or workbooks with data that you want
    > >>to include in your chart. Then, create the chart based on the summary range.
    > >> It will be much easier to maintain this way.
    > >>
    > >>----
    > >>Regards,
    > >>John Mansfield
    > >>http://www.pdbook.com
    > >>
    > >>"Mike" wrote:
    > >>
    > >>
    > >>>I have several worksheets that have a value located in the same cell on each
    > >>>worksheet. I would like to graph these values.
    > >>>
    > >>>Each worksheet is a monthly summary of activity for an event. Each
    > >>>worksheet includes simple average and sum calculations, and it is the value
    > >>>of the sum on each worksheet that I want to graph. To complicate this, I
    > >>>would liek to set it up so tht I can insert a new worksheet into the set and
    > >>>have it's number automatcially added to the graph.
    > >>>
    > >>>I've looked at ranges and it looks promising, but everythimg I try to define
    > >>>a graph data source I get an error that says something like "Reference is not
    > >>>valid"
    > >>>
    > >>>The data range I am attempting to use is: ='1:3'!$E$41
    > >>>
    > >>>Any suggestions or comments about what I am doing wrong?
    > >>>
    > >>>-Michael

    >


+ 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