Help required in dynamically creating a chart

[Stoffel24]

I poke values into excel from another package. The values that come through are a date, a time and a value at each time. I then create a formula that analyses the dates in column A. If there are 2 dates that are the same, it puts the first instance of that date in one row of column B and leaves subsequent rows blank.
Eg
Date New Date Time REsult
10/02 10/02 06:00:00 5
10/02 14:00:00 2
10/02 22:00:00 6
11/02 11/02 06:00:00 8
11/02 14:00:00 2
etc

The number of rows of data poked through is variable so the formula that calculates "New Date" is replicated down to row 65536. I now need to plot this data, with the y axis being labelled with the date and then the 3 times above. (ie multilevel labelling) If I use a column range to plot (eg Sheet1!B:B) the chart tries to plot too much and the end result is messy. So what I would like to do is to plot only the cells with data in them. I have managed to get the required range written into a cell using a formula. (Eg the value of cell j5 is "Sheet1!C1:C17&quot. However I have not figured out how to use this entry as a reference when plotting the chart. Am I going down the right track? Can you actually do that - use a cell entry as a range in the chart wizard? Or is there another way that I could do what I need to do? I hope someone can help. Many thanks.

 

[SkipVought]

hi, Stoffel24,

1. Get rid of 65,000+ formula cells. All you need is Date Time Result.

2. Use the Pivot Table wizard to create the data summary that you want. Here's an example...

                 Sum of REsult		
Date     Time            Total
10/2/02  6:00:00 AM      5
         2:00:00 PM      2
        10:00:00 PM      6
11/2/02  6:00:00 AM      8
         2:00:00 PM      2

If you need help with pivot table, give a shout -- it's EASY & POWERFUL! Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

BYW,
If you set up the data range for the PivotTable to be A:C, AND set the field attirbutes to deselect (blank) AND build your chart using the Chart Wizard...

then, whenever you modify your data source and RefreshData, your chart will reflect all changes to your data -- NO SWEAT! Skip,
metzgsk@voughtaircraft.com

 

[Stoffel24]

Skip thanks for the replies. I have managed to find a work around and this is what I did.
I named the 2 columns with the dates and times as follows by going to Insert, Name, define and typing:

=OFFSET(Sheet1!$B$1, 0,0,COUNTA(Sheet1!$A:$A)2)
The results column was defined as:
=OFFSET(Sheet1!$D$1, 0,0,COUNTA(Sheet1!$A:$A))

(Column A was filled with dates as is shown in the message above. There is always a date for every result so this was a good column to do the count on.)
I then used these in my chart wizard, so the data range was:
Sheet1!Date_time:Results
The x-axis label was Sheet1!Date_Time
The values were Sheet1!Results

This worked perfectly and using the offset function combined with the counta works so that no matter how many values there are in the column, the chart will plot them.

 

[SkipVought]

I still recommend that you get rid of all those formulae. A formula will invariably get deleted. They occupy space unnecessarily. For an ongoing application, formulae in spreadsheets are a headache! Skip,
metzgsk@voughtaircraft.com