Time values and time scale

[jlt199]

Hi guys, I have an emergency and I'm really hoping you can be kind enough to help. I've touched on the subject before in other threads, but this is a specific problem different to the others I have asked.

I have a workbook that contains two worksheets of data, more than likely with a different number of filled rows in each sheet.

I want to plot varaibles from each sheet is seperate graphs on a third sheet, with a common x-axis for each chart. The data in each chart is contained in a named range, the contents of which is controlled by two scroll bars, one that denotes the position of the start of the range and the other controls the number of points in a series.

I kinda get everything working if a use a number in the x-axis (Example_dataNumber.xls attached), but if I use time values it all goes to pot! I have to turn off the use of time scale, otherwise all data are posiitoned in the same place on the x-axis. However, if I turn it off the the x-axis are different for the chart containing data from different worksheets. For an illustration please see Example_time.xls attached.

I'm working in Excel 2003 and I'm aware that thing go a little to pot athestically if using 2007, but haven't got around to addressing this yet.

If anyone is able to help me get my x-axis aligned using time values I would be hugely appreciative.

I don't suppose it's too relevent, but I should let you know that this workbook is created using a macro from another workbook. But I'm sure if the problem can be addressed in these workbooks then I can ammend my macro with few problems.

[shg]

These are both line charts. Don't you want them to be scatter charts?

[jlt199]

That would be better, but try changing it to a scatter graph and playing with the scroll bars. For some reason scatter graphs won't rescale the x-axis as you alter the data being plotted.

[shg]

I don't know what you're looking to see, but it looks OK to me.

[jlt199]

You are right, it does.

However, I tried using scatter plots with a larger data set and the results were less good. Unless there is a setting I have missed

[shg]

I think Excel is not excellent at setting min and max scale values based on data, and you'd need to write code to set them more appropriately based on the min and max values of time for the dynamic range.

You could

o use some formulas to extract min and max time for the data onto ChartSheet

o use some more formulas to calculate what you think are appropriate min and max scale values using some FLOOR and CEILING functions

o record a macro that sets those scale values on both charts (you'd have to copy the values from the cells initially)

o edit the macro to set the values from those cells and test it

o invoke the macro from the scrollbar change events

[jlt199]

C19 and C20 of ChartSheet contain the first and last time in the current data series. They are also held in Column A of the other two worksheets.

I can't get the chart to take a value from the worksheet to use as the maximum value though. Can it be done without using a macro?

[shg]

Can it be done without using a macro?

Be nice if it could, but it can't.

[jlt199]

My workbook has been created using a macro from another workbook. Can I use that macro to write and place a macro in this workbook?

Or is there a way I can modify the original line graphs to work with time?

[shg]

I have no different suggestion than the one I made, sorry.