I believe I have a pretty complex problem, and I don't think pivots will work because they won't support XY charts?

My company receives a large volume of data - diagnostic information from test units - that are organized by serial number and time. We are measuring the performance of a unit over time.

Best case scenario is that we could have a raw data where a line item has the serial number, date stamp and diagnostic reading of the unit. We have units all over the world and need to be able to simply throw the line items from our techs in the field (in the data capture form) into our raw data, disregarding the order.

I know how to do a dynamically named range, and have accomplished creating them, so here is the need:
  • With the dynamically named range "Serial_numbers", create a series for the XY scatter plot for each unique Serial Number.
  • With the dynamically named range "Time", create the X-axis for the XY scatter plot (I have reduced the date stamp to be a days stamp, so it's just a numeric).
  • Finally, with the dyamically named range Performance, create the Y-axis for the scatter plot.


Any help would be appreciated. I'm getting nowhere. I believe explaining is enough but I can create an example if needed.