I have long been reluctant to post solutions on-line for the very reason that there’s always someone out there smarter than me with a better solutiuon that makes me look like a “Piker”. Well I finally decided to post a solution to a problem I found recently, only to find once again that I missed something and that the actual solution to a percieved problem was actually much easier than the solution I found. So here I go “Eating Crow” once again.
First I have to thank my friend Andy Pope (becoming friends after we stared at each other across a crowded bar near Covent Garden for nearly an hour before we had the courage to ask if we were the one who we were actually looking for – and then sharing a couple of great local beers on-tap with Mike Staunton and Bob Phillips as well – who I’m sure both knew about this) for providing me with the absolutely correct solution to the problem of Dynamic Ranges in Excel 2007 and 2010 Charts.
In my research on this topic I think I tried this technique without success but I must have done something wrong and thus went off on a tangent to use the old “Coded Solution” Method.
But , as Andy pointed out to me, to use a Dynamic Named Range as the source of a Series in a 2010 Chart, you need to enter it through the “Select Data” Button on the Design Tab of the Chart and for the Series as a Fully Qualified Reference to the Range:
Notice that the full name of the Host XLSX file is referenced in the reference to the CHT_DATA Dynamic Name Range, a Range that looks like:
and is a truly Dynamic range (that takes advantage of the inherent Array capabilities of Excel in a cool way BTW – thanx again Andy). Therefore it is based on a Range Name CHT_LABEL that looks like this:
that Dynamically changes the range and the countA of values in Column A increases or decreases. Awesome !!
If you are using a Range Name with a Worksheet Scope (as opposed to Workbook Scope like these Names) you only need reference the Worksheet name as opposed to the Workbook name). BUT YOU CANNOT ENTER ANY RANGE NAME IN AN EXCEL CHART UNLESS YOU USE A FULLY QUALIFIED REFERENCE.
So in conclusion you can pretty much ignore my previous post (which I will edit to refernce this Post instead). I do think it’s a cool VBA solution though and I will probably use this technique in some other context somewhere, so I don’t consider it all a waste – just a BIG embarassment 😦 …. Just kidding – just a small embarassment.
Thanx again and again Andy.
Thanks for the info. Dynamic ranges are also possible through indirect and Index functions. the problem with the offset function is that it is volatile
True … but I like it that way if it means I don’t have to worry about it 😉 …. Call me lazy …
Thanx for your post.
So is INDIRECT!
So is Index in special circumstances.
Index:Index is quasi volatile – so it will recalculate only on file open and close
Hi, how about once more for those who don’t know what the hell you are talking about, who have no idea how to use Excel, but have to use it due to circumstances.
Not sure what you mean? You have a strange way of asking for free advice … (??)
I dont think I’ll be going any further with this issue I’m afraid. Good luck.
I fund your articles really good.
I hate to be the one who tells you this, but there is an even easier way to do Dynamic Charts:- Base your chart on and Excel 2010 table! No range names required at all.
Interestingly, the chart dynamically picks up changes in the number of rows in the table, even the the series formula does not reference the table by name.
Give it a try, just define the example you showed as a table, create a new chart and voila, it’s dynamic.
Oh yes I am a BIG fan of Excel “Tables” and you are right that this would be MUCH easier if you use “Tables” and the references end up as Structured References to Fields in the “List” – absolutely !!
But sometimes implementing Lists is not the preferred solution and also there is the issue of moving “legacy” spreadsheets forward to 2010 where this would be an issue (unless you have the luxury of being able to rewrite the spreadsheet).
Thanx for your point, well taken.
Never be reluctent to post, there is always something to be learnt from what others post.
Peter, tables/lists are great if you only want the data to continually expand. DRs are needed to do subsets of data and switching of data areas used.
That’s the great thing about knowing Andy, as well as being a good drinking mate (never makes me feel I drink too much :)), he is GOOD!
And aren’t we all great fans of tables? Well, no, not completely. Don’t you just hate how you drag a structured reference formula to the next column and the column you reference in the table changes, no way to lock as with absolute columns that I can see. And as for trying to use structured references in conditional formatting, forget it.
Yeah Bob … Andy is my new hero and a good beer-drinking mate as well.
Speaking of which …… It looks like I might be “Over ‘ome” sometime in mid-June. Maybe we should have another beer (and hopefully I won’t be so sick this time 😦 ).
Will keep all posted.
Depends when for me. I am away until June 10th, up in London to see a play on 17th, and a couple of other things that escape my fading memory. I am sure Marcus will be there though!
You’re truly right on this blog
Thanx for that… 🙂
Extremely well executed writing
Thanx …. much appreciated.
Wow! I’ve been struggling with dynamic charts in 2010 for what seems like an age! I’ve finally found your blog and realised I need to fully qualify the global names!
You’re welcome 🙂 … Too bad you had to waste so much time with this because nobody at Microsoft thought it was important enough to tell anybody about it ….
Hate to dig up an old post, but thought you might be interested to know that this solution does not work on my version of Excel 2010 (32 bit). Maybe they REALLY don’t want us to use dynamic ranges in charts and “fixed” it in a recent upgrade. Whatever the cause, it is incredibly frustrating.
I don;t want to dig up an old post either but I have to say that the testing I did was on 2010 so I think (with all due respect) you musta done something wrong?;-)
I’m not blogging anymore (for various reasons) so I’m not interested in starting a big thread on this but I suggest you try again.
Hi. I’m trying to design a template of sorts for the first time, and I need to create multiple charts in the same format…but with different data sets. How do I do this?
Hey Nicky …
I suggest you create your master chart and then copy and paste it as many times as necessary. Then you go in a change the data source of each chart, pointing it to your other data sources in the model.
It’s actually easier than you think.
p.s I watched “Little Nicky” last week again and I’m still laughing, especially at the pineapple/Hitler bit. I assume you’ve been forced to watch it somewhere along the way 😉 ?
Everything is very open with a clear clarification of the issues.
It was truly informative. Your site is useful.
Thanks for sharing!
First off I would like to say terrific blog! I had a quick question that I’d like to
ask if you don’t mind. I was interested to know how you center yourself and clear your
head before writing. I have had a difficult time clearing my thoughts in getting
my ideas out there. I do enjoy writing but it just
seems like the first 10 to 15 minutes are usually lost just trying to
figure out how to begin. Any recommendations or tips?
No answer to that one … I’ve never had writer’s block like that … probably because I only write when something’s on my mind 🙂 ..
I’m impressed, I must say. Seldom do I encounter a blog that’s both educative and amusing, and let me tell you, you
have hit the nail on the head. The issue is something that too few folks are
speaking intelligently about. Now i’m very happy that I found
this in my search for something concerning this.