[EN] - Excel - Dynamic Chart Range with Named Ranges

Well, I usually have a problem creating excel dashboards for management professionals (users who don't know and don't want to now how excel works or where to edit). I can create the dashboard on one sheet, but if the data differs in size (more rows than  initially prepared for) the charts don't are reliable.
Sometimes this can be managed, but more often than not, no.

So after a bit of googling I discovered "named ranges". This is very interesting for one simple reason: with INDIRECT and some juggling I could create the string with the desired range, but the "Select Data" window for the chart didn't allow me to enter a reference: just a range.

HOWEVER, with named ranges this can be done. Instead of (for example) =Sheet1!E4:E270 ,just type (for example) =Sheet1!namedrange1 (assuming there was a named range with this name).
this way, you can keep adding new rows and the chart update automatically. My code, used COUNTIF with a max value of 370 but this can be adjusted.

I created two named ranges (NR) for this post: x_axis and y_axis (I believe self explanatory). And the value for the range reads a cell on the spreadsheet (this is just because it is easier to edit the spreadsheet text than in the NR dialog window). These values are the ones in the top of the following screen like:

 x_axis Sheet1!$d$6:$d$23

created with:

 =("Sheet1!$d$6:$d$"&(5+COUNTIF(Sheet1!$E$6:$E$369;"<>")))

After entering new values below the 34 130 row, the chart updates automatically. This is GREAT!!!

My formula is different from the one I found on

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=518

but it works great all the same.

I hope this is clear. If not, leave a comment and I'll get back to you.

IMPORTANT: NR don't react well to the deleting/ inserting of new columns/rows. Beware.