way to auto change data range when query is updated?

Hi there

I'm working on a fairly complicated plot that I've run out of ideas on. What I have is a database of measurements taken, where X-axis is distance across a surface and Y-axis is measuring deformation to that surface. Each measurement is actually a group of data points from one sweep across the surface on a certain day, resulting in a line plot for that group. What I'm trying to do is create a "checksheet" so that you can overlay plots from multiple dates on the same grid to compare how the surface has changed with each measurement. I've got the checksheet part working properly, and I can toggle things on/off as I like as long as my data doesn't change.

The rub is that this data is gathered via query, and each measurement doesn't have the same # of data points. If I update the query to look at a different date range, the plots are now off because the old data range doesn't match the new data.

Is there any way of changing the data range when a query gets updated, or a way to tie it to a function?

Interesting Problem. Please post a sample data file containing two X-Y sets of data that are incompatible. It will be easier to help you that way.

Lewis

Yeah, I was trying to find the best way to show you my issues without ruining everything and I think I have something. So please see the attached.

The "Profile Report" tab has a bunch of data that's blank now, but the important part is the plot and checkboxes. The query that the plot is getting data from is from the "data" tab. The full spreadsheet is a query, and instead of just two profiles it can in theory pull all the data that we want, but I only made room for 12. The checkboxes are setup to recognize the start of the different profiles and those all work fine.

As it sits right now, the checkboxes and data works fine. However if you look at the "data2" tab, you can see a quick example of what doing a different query could result in. Each measurement may have a different amount of points, so what worked for the plot in "data" now won't be correct for "data2". You can copy the table in "data2" to "data" to show what happens in this case.

What I want (if possible) is for the data ranges to change with the query. I already have a way to detect the next batch of measurements as that's how the checkboxes work, so I'm not sure if I can make use of that in any way.

Take a look at Sheet 'data2' of the attached file (Excel 2003 format), which should work for your version of Excel. I put sample data in rows 33 to 53. The format of the graph is similar to yours with the except that:
1. On that for X axis range, I delcare all rows 33 to 53 (range 0 to 100) whether they are used or not.
2. On the Y axis range, I declare only the data points that are used (or the entire 21 points can be declared if you like).

What I was really interested in when I asked the question was your X axis. As I hoped, all your X axis points are the same for each graph. Please note that only NON-BLANK cells are graphed. In my sample, I provided a 3rd set of data points named 'junk'. It contained an 'outlier' point that I blanked out and it was omitted from the graph.

To implement what I've done:
a. Store the X axis somewhere on your Worksheet.
b. The Y axis for each series doesn't have to be copied, it can just be declared to be in wherever it currently resides.

I hope this answers your question.

Lewis

Thank you, that solves most of my problem and gives me some more ideas to continue. I didn't think about how the x-axis are always the same which is true, so that'll help me out a lot. The problem I still have is that the plots will still use a static range for the Y-axis, which will change when a query is updated and have to be manually updated. However, I think using a constant X-axis and some lookup fuctions I'll be able to construct the same Y-axis table that you made which should update regardless of how the query turns out. I'll post what I have if I can get it to work out without manually changing the Y-axis range every time.

I haven't been able to get any further than this. I'm still struggling to find a way to get the plot to update when the query changes the data range. I'm sifting through code examples to try and build a macro but I'm not strong in this area.

I can't help you because I don't understand what you want. If you can explain based on the data you posted,or if you post a new worksheet with the raw query data, I can probably help or at least point you in the right direction.

Lewis