Excel chart source data - how not to plot blank fields? 1

[OMoore]

Hi all,

I have an excel spreadsheet containing data that I graph. The data is updated daily and I then have to update the graphs manually daily. I can select a lot of blank rows for the data series which will be filled in over time. However, this obviously makes my graph look wrong as the blank rows are being plottedd. How can I set up the graph so that it will only plot non blank fields?

Thanks
Owen

 

[BlueHorizon]

Hi Owen,
What version of Excel are you using? Can you explain better why you "select a lot of blank rows for the data series which will be filled in over time"?

If your chart is on the same page as the data, is it possible for you to use the click and drag method to add data series to the graph? This would simplify the process and perhaps eliminate the need for blank rows.
HTH,

Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[OMoore]

Thanks for the reply Blue.

Say my graphed data is in fields A1, A2 and A3. Every day, operators fill in more data...A5, A6 etc.
The graphed output is for values A1, A2 and A3. In this way my graph is complete with no blank spaces on the X axis.
Now in order to update the graph for values A5, A6 etc I have to manually change the source data every day. I will not always be available to do this, but the reviewers of the information may at any time want the infomation and read from the graph - I need to ensure that the graph is fully updated at all times.

One way of having the graph updated is to select rows A1 to A30 while I am first setting up the graph. In this way my first 3 points along the x axis (A1,A2,A3) are on the graph but the remaining 27 points are blank on the graph. I only want to graph actual points (and after field A30 is reached I will have to update my data series)

I hope this has been a little clearer than my previous post.

Thanks
Owen

 

[GlennUK]

Have you ever used dynamic named ranges?

You could have your chart series designated by dynamic named ranges that adjust to the number of values that exist.

Do Insert/Name/Define, and choose a name for the data series, say MyData, and have a definition of:

=OFFSET(A1,0,0,COUNTA(A1:A30),1)

Have a play around with this kind of approach and see if it suits you.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[OMoore]

Glen,

Thanks for the help! I was able to get it working using the following:
=OFFSET(INDIRECT("'Inner Barex Actual Data'!$R$4"),0,0,COUNTA('Inner Barex Actual Data'!$R:$R)-1,1)

Again thanks very much - I haven't come across dynamic ranges much but can see a very good use for them!!

Owen