Want to plot a graph using data from 2 different worksheets in sam
 

workbook.

I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in the
same file with different worksheet, I cant seem to plot it.

I had to copy all the data on same worksheet and then plot it. But there
must be a way to do it without this action.

Try charting one year then: Chart; Source Data; Add to add the rest.


"cteq" wrote in message
...
workbook.

I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in
the
same file with different worksheet, I cant seem to plot it.

I had to copy all the data on same worksheet and then plot it. But there
must be a way to do it without this action.

I can do one year no problem at all.

"Steve" wrote:

Try charting one year then: Chart; Source Data; Add to add the rest.


"cteq" wrote in message
...
workbook.

I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in
the
same file with different worksheet, I cant seem to plot it.

I had to copy all the data on same worksheet and then plot it. But there
must be a way to do it without this action.

Try some code:

'before you start, create a blank chart sheet in your workbook


Option Base 1
Sub addSeriesFromAllSheets()

Charts("chart1").Activate ' rename chart1 to match the name of your new,
blank chart
'ensure the chart is blank
RemoveData

For Each ws In Worksheets
If IsNumeric(Right(ws.Name, 4)) Then 'use a sheet naming convention
'so you miss those you don't wish to chart.
'in this case, if the last four characters are numbers, it data will plot on
the chart
ActiveChart.SeriesCollection.Add _
Source:=ws.Range("A1:B6") ' Edit this so it covers the range in
the sheets you wish to chart
End If
Next ws
End Sub
Sub RemoveData()
On Error GoTo Exit_RemoveData
While ActiveChart.SeriesCollection.Count 0

ActiveChart.SeriesCollection(1).Select
Selection.Delete
Wend
Exit_RemoveData:
End Sub

"cteq" wrote in message
...
I can do one year no problem at all.

"Steve" wrote:

Try charting one year then: Chart; Source Data; Add to add the rest.


"cteq" wrote in message
...
workbook.

I want to take 2003, 2004 sales and 2005 sales on same graph. Data is
in
the
same file with different worksheet, I cant seem to plot it.

I had to copy all the data on same worksheet and then plot it. But
there
must be a way to do it without this action.

Name your ranges (Sales2002, Sales2003, etc), then add them to the chart
preceeded by the file name...

eg

Series 1 Values = Sales.xls!2002Sales
Series 2 Values = Sales.xls!2003Sales
etc...

will do the trick...

Hth,
Oli

--
Message posted via http://www.officekb.com

This page might help:

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

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

cteq wrote:

workbook.

I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in the
same file with different worksheet, I cant seem to plot it.

I had to copy all the data on same worksheet and then plot it. But there
must be a way to do it without this action.