Dynamic Range Names And Charts In Excel 2010 (The Right Way)

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:

=OFFSET(CHT_LABEL,0,1)

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:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

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.

Dick