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

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

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

Mike -

I have posted a method he

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

Thank you, Thank you, Thank you!

This is a WONDERFUL place for advice!

-Michael

"Jon Peltier" wrote:

Mike -

I have posted a method he

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