Dynamic Chart Range displaying only the first value

[rituna]

Hi,

I'm trying to make a dynamic chart that would only display data up to the current month, i.e. whenever data for a new month are entered, the chart range should update automatically based on named formulas. The data looks as follows:

month	stage
I.15	2.2%
II.15	1.9%
III.15	1.5%
IV.15	2.0%
V.15	2.0%
VI.15	1.9%
VII.15	2.1%
VIII.15	2.3%
IX.15	2.2%
X.15	2.2%
XI.15	2.4%
XII.15	2.7%
I.16	2.7%
II.16
III.16
IV.16
V.16
VI.16
VII.16
VIII.16
IX.16
X.16
XI.16
XII.16

<tbody>
</tbody>

and I created two named formulas such as

graph_range  =OFFSET(A2;0;0;COUNT(B2:B25))

graph_labels  =OFFSET(B2;0;0;COUNT(B2:B25))

COUNT(B2:B25) correctly resolves to 13 right now.

Then I entered =SERIES(customers!$D$1;customers.xlsx!graph_labels;customers.xlsx!graph_range;1)

The book is called customers.xlsx and the sheet is named "customers".

The problem is that instead of 13 values, only one value is displayed in each cell containing the named formulas (and therefore in the chart too). Even if I try to enter it as an array formula with ctrl+shift+click, it still displays only one value.

I have no idea what I'm missing, any help hugely appreciated!

r.

 

[rituna]

Nobody has any possible solution?

 

[Jon Peltier]

You've defined graph_range as column A and graph_labels as column B. I think these should be reversed.

I don't know what this means:
"The problem is that instead of 13 values, only one value is displayed in each cell containing the named formulas (and therefore in the chart too). Even if I try to enter it as an array formula with ctrl+shift+click, it still displays only one value."

 

[rituna]

You've defined graph_range as column A and graph_labels as column B. I think these should be reversed.

I don't know what this means:
"The problem is that instead of 13 values, only one value is displayed in each cell containing the named formulas (and therefore in the chart too). Even if I try to enter it as an array formula with ctrl+shift+click, it still displays only one value."

I already solved the problem. It turned out that I was naming a cell containing the function instead of creating the function with name manager.

Thanks for your reply anyway.

r.