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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.