Confusion with graph data...

[RobbieC]

Hi there, I am trying to put 2 graphs together based on inputted data on Sheet 'DATA'. There are 40 possible inputs. Not all may be used...

The columns I'm interested in are E & F for one graph and E & I for another.

When I input the data series for Depth (E) & Cum N (F) I use:

=DATA!$F$2:$F$41 - (x-series)
=DATA!$E$2:$E$41 - (y-series)

This plots this graph which is good

However, when I try to plot the data series for Depth (E) & CBR (I) I use:

=DATA!$I$2:$I$41 - (x-series)
=DATA!$E$2:$E$41 - (y-series)

it plots the same graph as above when it should be looking like:

which I was able to plot by changing the data series (on x) to:

=DATA!$I$2:$I$21 - (x-series)
=DATA!$E$2:$E$41 - (y-series)

So it appears that the 'blank' entries (after the initial 20 values) are making it throw a wobbly...

Can anyone help throw some light on this for me? How can I make my graph show the correct curve regardless as to how many values are inputted?

Thanks very much for your time

Rob

 

[offthelip]

I have found Excel graph control to be difficult and very poor. I had a similar problem and I put some code into the worksheet change event to update the graph automatically to tie up with the actual data . This is a graph in columns E and F of my sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Application.EnableEvents = False
   
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Range(Cells(1, 5), Cells(lastrow, 6))
    Application.EnableEvents = True

End Sub

 

[RobbieC]

Thanks offthelip, that is just the ticket! Great way to work with dynamic ranges!

Thanks again for your help!