Chart won't accept dynamic named range

[CRentek]

I'm setting something that allows the user to compare bond yield curves on a graph. The setup involves a dropdown to select the final year of data the user wants to see and several date drop-downs.

For example, if the user wants to compare the current yield curve out to 2031 against how the same curve looked the end of Q1 2011 and Q2 2011, he would select "2031" in the first dropdown and then select "4/1/2011", "7/1/2011", and "9/8/2011" in the other three dropdowns.

The data is on a separate sheet, and mechanics of my named ranges are sound. However, Excel won't allow me to add my named ranges to my graph.

As is, the series looks like this:

=SERIES($L$5,'2011'!$B$3:$B$25,'2011'!$C$3:$C$25,1)

I'm trying to replace $B$3:$B$25 with CRV_DATES and $C$3:$C$25 with CRV_ONE (when I get this to work I'll add the other curve to the chart). When I try to add them, though, I can't get out of the series dialogue. When I hit enter, nothing happens--no error message, no nothing. The change simply won't "stick". I'm completely stumped and would appreciate any advice on how I should tackle this. I've never posted here, so if I'm omitting any info that would be useful to you please let me know.

Here are my primary named ranges and the ranges/functions I use within them:

CRV_DATES: =OFFSET('2011'!$B$3,0,0,FindCurve,1)
CRV_ONE: =OFFSET('2011'!$C$2,1,MATCH(DATE_ONE,StartDateRange,0)-1,FindCurve,1)

FindCurve: =MATCH(CRV_END,MaturityRange,0)
StartDateRange: =OFFSET('2011'!$C$2,0,0,1,COUNTA('2011'!$2:$2))
MaturityRange: =OFFSET('2011'!$B$3,0,0,30,1)
CRV_END: =Sheet1!$L$3

 

[Steve=True]

Hi CRentek,

Welcome to the forum!

Check out my tutorial and let me know what you think.
http://www.exceldashboardtemplates.com/?p=688

Your problems are one or some of these.

1) You can't put the dynamic formula in the Chart Data Range field as it will be converted to a static range.

2) You can't put the dynamic offset function directly into a series values field as this will cause an error. You can put in Names (which looks like you did)

3) Finally, you have to put in the full file name with the named range in the series value field when using offset function in order to not get an error and not be converted to a static range.
i.e. =exceldashboardtemplates.xlsx!CRV_ONE
i.e. if you have spaces in the file name:
='excel dashboard templates.xlsx'!CRV_ONE

Hope you can get it to work!

Steve=True

 

[LeeKennedy]

1) You can't put the dynamic formula in the Chart Data Range field as it will be converted to a static range.

2) You can't put the dynamic offset function directly into a series values field as this will cause an error. You can put in Names (which looks like you did)

3) Finally, you have to put in the full file name with the named range in the series value field when using offset function in order to not get an error and not be converted to a static range.
i.e. =exceldashboardtemplates.xlsx!CRV_ONE
i.e. if you have spaces in the file name:
='excel dashboard templates.xlsx'!CRV_ONE

Hope you can get it to work!

Steve=True

Hi Steve. I'm a long-time reader of the site, but I'm getting better at it and now have to ask my own questions!

I'm having a problem with using a Named OFFSET function as a series value in a chart. It's worked on a number of other spreadsheets, but for some reason, this latest one won't work.

It's a simple problem: when I enter in the Named function (including the file name and everything), it won't let me hit enter and activate it. Everything is grayed out and I can't click on anything else! The only thing it will let me do is hit escape and cancel the edit. So weird! Any idea what could be the problem here?

Here is the SERIES formula so you can have a look ("filename" used as replacement for actual file name. everything else is exact):
=SERIES(Rankings!$B$7,'filename.xlsx'!RankingsDates,Rankings!$F$7:$S$7,1)


EDIT (of course I found the answer 2 seconds after posting this): I'll give my answer here in case anyone else finds this! You have to change the name of the Named Functions. This might be because I have the same Named Function being used with a different report, and so the names have to be unique.

Thanks!
Lee