dynamic chart for xls data

Hi ,

Attached is an xlsx sheet with various metocean data for a data buoy.  I would like to be able to analyse this data through use of dynamic charts for specific periods or dates and specific column headings eg windspeed , wave height etc. Can you please assist in how I may go about creating such dynamic charts so graphs presented more interactive and meaningful.  The recorded data would be expanding so like capability to auto  update as new data inserted in columns etc.

Finally is it possible to create output charts for all headings an say one dashboard like page for quick review for specified period say a year or month or even specific day??

I am using windows 7, office 2010.

Thanks in advance for assistance.
Who is Participating?
Glenn RayExcel VBA DeveloperCommented:
Using dynamic range names, one can modify charts to update in real-time based on data like you've requested.

For example, if one wants to update chart data based on specified Start and End dates, it's possible to create an OFFSET/MATCH formula in a range name that finds those dates (or closest matching) in a range of dates and then change the plot ranges accordingly.

The attached workbook shows an example of this.  Creating these kinds of dynamic range names is tricky and there are two things one needs to note:
1) The range names are impossible to test in an Excel workbook; the result will always be #VALUE!  However, when the range function is correctly built and you click on that formula, the selection will be highlighted (marquee):
successful formula in range name2) When you replace the graph ranges with these dynamic range names, you MUST also specify the workbook filename.  This is not intuitive; you just have to do it.

Here are the three range names I created:
=OFFSET('_APR15_Dynamic Chart'!$B$1,MATCH(StDt,'_APR15_Dynamic Chart'!$B:$B,1)-1,0,MATCH(EndDt,'_APR15_Dynamic Chart'!$B:$B,1)-MATCH(StDt,'_APR15_Dynamic Chart'!$B:$B,1),1)
=OFFSET('_APR15_Dynamic Chart'!$E$1,MATCH(StDt,'_APR15_Dynamic Chart'!$B:$B,1)-1,0,MATCH(EndDt,'_APR15_Dynamic Chart'!$B:$B,1)-MATCH(StDt,'_APR15_Dynamic Chart'!$B:$B,1),1)
=OFFSET('_APR15_Dynamic Chart'!$F$1,MATCH(StDt,'_APR15_Dynamic Chart'!$B:$B,1)-1,0,MATCH(EndDt,'_APR15_Dynamic Chart'!$B:$B,1)-MATCH(StDt,'_APR15_Dynamic Chart'!$B:$B,1),1)

"StDt" and "EndDt" are regular range names pointing to cells B2 and B3 on a new report sheet.  The only significant difference between the formulas is the OFFSET reference cell (the first argument).

After these were created, the chart plot data was updated.  For both charts, the axis label range was changed to:

and for each chart, the data range was changed to:

Lastly, a new sheet was added as both a user interface - to enter the dates - and a display for both charts.  Changing either of the dates automatically updates the charts to the same range.

Using this, one could extrapolate and add respective charts for the other metrics in your data.

PVR101Author Commented:
Glenn, many thanks for reply and sheet.  

I am new to using name ranges etc but have attempted to create a new range for waveHeight by using 'new' and following gust /wind expamples and then editing the reference offset cell accordingly eg. ($G$1,) which appears to work.  Is their a quicker way to copy and edit or just this process for other dataset heading ranges.

When creating new plots such as for new waveheight range - do i just copy existing plot and manually edit  under select data option and then 'edit' under legend entries(series) and manually changing series name and typing in new range name (or work book name) ? is this the quickest only way to do this?

I tried editing the start - end dates to include all available data but keep getting error message Screenshot.png. do you know why this might be?

I noted that the current data range does not appear to allow plotting for a single day as just returns a vertical line plot. Is this just a formatting issue?

Not wanting to further complicate matters but is it possible to say specify a month/week/day period but for all the years of available data i.e. jan04,jan05,jan06 etc as this information would be more informative than maybe just a particular month in a particlaur year which is really just a snapshot view rather than representive conditions for a given period.

Thanks again.
PVR101Author Commented:

It seems the issue with plotting the full dataset plot was format related as once I modifeid the plot it is now produced when you use first/last data as start/end dates.

Similariliy tweaking the fomats and chart type to xy scatter ( from line) the chart now correctly plots daily results - although does plot missing or 0 data.  

By applying the simple fiter drop down bar under the time column on dynamic chart sheet tab and using the date filters>all dates in the period>month I have managed to produce relevant plots for full dataset when first/ last dates specified.  Just wonder if 'plots' sheet can be modified somehow to incorporate another input cell for say 'month' (maybe a drop down bar option to select specific month) so this additional filter would apply if specified together with start/end dates ( cell box would be left blank otherwise to run as currently).
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

PVR101Author Commented:
hi Glenn, just wondering before close out Q if it is possible to ammend plot period to filter for month or day as mentioned above.
Glenn RayExcel VBA DeveloperCommented:

I apologize for the delay in my reply.

1) If you choose the same date, then no range can be determined for the plot or date ranges, so you get an error.  To show one day of data you have to select two adjacent dates (ex., 10/1/2006 and 10/2/2006).

2) You correctly diagnosed and resolved the issue with the daily plots by changing from line to X-Y charts.

3) It's not hard to change the UI to allow the selection of an entire calendar month and there are many ways to go about it.  I've attached a revised version of your workbook that demonstrates one method (using data validation to pick a month-year from a list and then return the start and end dates to plot).

PVR101Author Commented:
no problem Glenn and thanks again for clarifications.

Whilst revised sheet is very helpful and apologies if misunderstood me but for my purposes ideally looking to plot month for all years of available data - so for example if looking at the month of May then able to select month and the plots will auto update for specified month through all of the available dataset years eg 2004,2005,2006 as in sample sheet.
Glenn RayExcel VBA DeveloperCommented:
Sorry, I didn't understand that request.  

To view several plots over the same month timeframe (i.e., same calendar month across several years) will require not only a significant change in the UI, but also the actual data plotting/chart types.  That's beyond the scope of the original question which was to create dynamic charts based on your original example.

I'll be glad to address a new question regarding dynamic display of data given this new definition.

PVR101Author Commented:
Glenn - Thanks for assistance and guidance.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.