Excel Chart Referencing Other Workbooks

**alecp** · 06-29-2011, 04:29 PM

Hello there,

I have an project for another department where I am creating a excel file that charts the data that they collect each day. I need it to be a separate file where it references the data they collect for each of the parameters I need. I have come across two major issues:

1: There are two files I need to reference for the same series (this means 2 references for each x of each series and 2 references for each y of each series). I know how to do this normally (i.e. =SERIES("Name",(sheet1!cellsforx, sheet1!othercellsforx, ....etc) and I have successfully referenced the first file, but excel will not let me reference both of them in one series for some reason. An invalid path message comes up. Is it possible to do? Am I just typing it in incorrectly? If not, what is the easiest way of doing it?

2: The x values are dates, and they are already typed in across the spreadsheet. The y values are typed in everyday once they are collected. This, however, results in excel spacing out the x-axis for the full 6 months that are going to be recorded, but no data plotted (I have them as #N/A from an IF formula so they don't show up.) Is there a way to get the axis to expand ONLY when the data is collected for that day?

Microsoft Excel 2003

Any help/suggestions will be very welcome. I'm new at this forum, so please bear with me.

Thanks,

Alec

**Andy Pope** · 06-30-2011, 04:17 AM

You may find it easier to build a set of chart data in a worksheet of the chart file. It will be easier to reference other workbooks from the cells rather than the chart.
Then you can use named ranges to control the information in the chart.

**alecp** · 06-30-2011, 08:42 AM

Okay I have pulled them into a data tab in the new file, but now how do i name the ranges so that the charts will not space out automatically for the days without y values yet? If you want me to attach a mock excel file to better understand what i mean I could do that