Microsoft Excel and Dynamic Charts

Community Forums/General Help/Microsoft Excel and Dynamic Charts

_PJ_(Posted 2012) [#1]
I've seen umpteen tutorials across the web, but none of these seem o help my specific situation.

Basically, my issue is that the Excel charts seem to convert Named references to absolute references, therefore increasing volatility of the source.
Also, in situations where the source data range is formed from spreadsheet calculations, to allow for additional entries to be calculated as they are appended to the range, the entire possible range falls under the name. This means, that values contaning Null entries forming the calculations which result in null or 0 results (particularly, say, multiplication or division) will be considered as valid when forming chart source, and therefore, the chart always maximises its data series despite only a few true valid entries existing.

Although I've attemp0terd to restrict the source data ranges by use of another sheet with specialised cells to identify the beginning, end and hopefully relative offsets in order to identify just the data sets needed for the chart - I still can't seem to get it to work...

I know this sounds complicated, though it's only concerning the basic fundamentals of Excel, but it should be much clearer if you see the simplified example I've uploaded to highlight my problem.

Not sure what kinda compatibility is required, but I see no reason why it shouldn't run on at least '97 versions and up - it was created with Excel from the Office XP package

http://homepage.ntlworld.com/mickyandlaura/Temp.xls


NOTE- There should be no need to activate Macros on this file. I have confidence in its 'safety' for opening in that I keep my PC pretty secure, but it would be adviseable to always use virus-scanner and never allow macros to run in files that you download. :)


If anyone knows how to address this problem, please let me know!


Thanks!


(Edit - Fixed link)

Last edited 2012


_PJ_(Posted 2012) [#2]
In the end, I had ot resort to a bit of a kludge.

It appears there's no way (except perhaps dynamically rebuilding the entire chart with VB each and every time) for the charts to read dynamic data or from Named ranges.

IThe first step to a solution involved limiting the number of data elements I would chart. For practical purposes, I chose a limit of 24 rows/entries OR dates from the last 2 years - whichever was the lower. This allowed me to then construct a hidden more static spreadsheet containing direct linsk to the (up to 24) entries. Although the information in this new spreadsheet referred to the content of the source data dynamically and by Name, the actual cell references for this hidden sheet could be referred to absolutely by either RC or A1 style references.
By providing a 'reference point' starting cell between this hidden data and the source, I could make use of the Offset function to use relative references within my hidden static sheet.

The chart could then be generated from the more static sheet, which itself pulled the relevant dynamic data in.
*phew*