Dynamic Chart

(Sorry. Meant to post this under charting. Could a mod please move it for me? Thanks.)
How's it going, Gurus.

I've tried everything I know to make this chart graph a dynamic range of data. I've searched this site, googled it, and read numerous articles, but I'm apparently missing something. Every formula I've tried gives me an error.

All I'm looking to do is have this chart (attached) update automaticially when I insert cells and new data before the range B21:B51. Here's the formula I've tried for the TOP line on the chart, "NASHVILLE".

HTML Code: 

I get the error, "That function is not valid".

What am I doing wrong?

Thanks in advance for any help you can offer.

Hi,

this will work best if you use range names for your data series and make the range names dynamic. Use Insert - Name - Define to create range names for each series, using the OFFSET function in the range name description to make it dynamic.

syntax =OFFSET(reference, rows, cols, height, width)
Example range name definition, let's say you call the range "MyRange":

=OFFSET(A24,0,1,1,COUNTA(24:24)-1)

explanation:
offset reference = A24 (reference point of your offset definition)
offset rows = 0 (stay in the same row)
offset cols = 1 (move one column to the right)
height of range = 1
width of range = count the number of cells with content and subtract one for the text in column A

You can now use the range name "MyRange" in the data source for the series, BUT you have to use the following naming convention:

='ExcelFileName.xls'!MyRange

If you enter just =MyRange into the data source field, it will throw an error message.

You'll need to create dynamic range names for all your data series, but you could use a formula like

=OFFSET(MyRange,1,0)

for the second series, so you only define a base series and offset all other series from that one.

Understand?

Your problem is the references in your named ranges are relative, so will keep changing. You need to make them absolute.

So ONTARIO

=OFFSET('Hub In Stock %'!$A$24,0,1,1,COUNT('Hub In Stock %'!$24:$24))

I added another named range to do the category axis labels.

CLABELS: =OFFSET(ONTARIO,-1,0)


Finally series formula would be,

=SERIES('Hub In Stock %'!$A$24,'Dynamic Chart.xls'!CLABELS,'Dynamic Chart.xls'!ONTARIO,1)

Select the chart

Chart Tools > Design > Select Data > Edit

which series had data labels applied?
were all points labeled?