Charting in Excel 2007

Question

I am trying to create a chart using data from 2 separate spreadsheets. My first spreadsheet has data from 1998 to 2010. The 2nd sheet has data from 2010 forward. The date is in Col. A of each sheet and this should be the bottom (X) axis. The data is in Col. G on the first sheet and Col. AP of the second. I know you can define data series from different sheets by just adding a series, but I cannot get the date axis to combine the dates from the 2 sheets. I have tried to string them using commas, colons, and semi colons, but I get error messages. Is there a way to do this?

Answer

Your best bet would be to set up another range which picks its values from the 2 sources as one contiguous range, then use this as the source of your chart. This "theoretically" should work as a SERIES formula, but doesn't: =SERIES(Sheet2!$G$1,(Sheet2!$A$2:$A$14,Sheet3!$A$2:$A$9),(Sheet2!$G$2:$G$14,Sheet3!$AP$2:$AP$9),1)

Question

I am trying to create a chart using data from 2 separate spreadsheets. My first spreadsheet has data from 1998 to 2010. The 2nd sheet has data from 2010 forward. The date is in Col. A of each sheet and this should be the bottom axis. The data is in Col. G on the first sheet and Col. AP of the second. I know you can define data series from different sheets by just adding a series, but I cannot get the date axis to combine the dates from the 2 sheets. I have tried to string them using commas, colons, and semi colons, but I get error messages. Is there a way to do this?

Answer

Robert,

I used an XY scatter plot.

I put the plot in one of the workbooks. In the same workbook that contained the graph I created two defined names

XValues YValues that refered to the data in the other workbook

I then created two series on the graph/chart - one for the local data and one using the defined names (nothing special about the names I chose - just so I could recognize which was X and which was Y).

that worked for me.