Dynamic XY Chart for multiple Channels of data and multiple axes

**bjsebeck** · 02-17-2015, 04:21 PM

I'm working on a sheet to help my Lab Technicians process the data from some monitoring software at the request of my manager and having some issues.

The way that our software records data is to record each of 6 channels of data for a given time, then to record the same 6 channels for the next timestamp, kind of like the sample shown below.

Capture.JPG

What I'd like to do is create a single chart on a separate sheet that is an XY chart with the time as X with Temp, Concentration, and the two spec limits as different Y series and have Temp on a secondary axis. I'd like to be able to, from the chart, select what channel is being plotted. If I can make it so that the chart title updates with the channel selected and include the Serial Number of the part, that would be great as well. Though, for one reason or another, our monitoring software doesn't record the serial number every time it records data.

Any suggestions for how to go about this? I last wrote a macro in Excel 2003, but it's been over a decade since I've done that and I've tried with a pivot chart, but from what I can find it appears that XY plotting doesn't work with a pivot chart.

**MrShorty** · 02-17-2015, 04:52 PM

You are correct, that a pivot chart cannot be a scatter chart.

Here's a tutorial/discussion on how to make "regular" charts (including XY scatter charts) from pivot tables. http://peltiertech.com/regular-chart...-pivot-tables/ If you feel like a pivot table will be the easiest way to select a given channel for the chart, then this should help you build a scatter chart from the pivot table.

Another thought might be to make us of autofilter to filter the list. https://support.office.com/en-US/Art...rs=en-US&ad=US Usually, when data is filtered like this, the chart will only show the "visible" data left by the filter.

**bjsebeck** · 02-17-2015, 05:01 PM

Thanks for the help.

My issue is that sometimes we present the data from this sheet to a customer and we'd like to avoid clicking away from the chart to the data, changing the auto filter and changing back to the chart. If there were a way to do it from the chart, I'd pretty much be in business, but I haven't been able to find a way to do that.

**MrShorty** · 02-17-2015, 05:52 PM

The chart itself will not process data for us like this. Charts are good for displaying data that has been processed in the spreadsheet, but they do not have many built in tools for processing data.

The only thing I can think to avoid clicking away from the chart is to have macros associated with buttons on the chart that will perform these kind of operations for you. These kind of macros are way above my pay grade, so we will have to see if someone will step in and help with the macro part of this.

**bjsebeck** · 02-17-2015, 06:05 PM

Yeah, ultimately my issue is that our software will monitor up to 16 channels of data and we need all of that to be charted in a way that will only show one channel at a time.

Originally, I had developed a sheet using a bunch of helper columns and INDEX and MATCH to sort out data based on channel and had 16 chart sheets. Problem being that led to a file that was 130MB. It was completely unwieldy and crashed Excel regularly. So, I got assigned this task of streamlining the whole thing and reducing file size, but still doing everything that the other sheet did.