Thread: Dynamic Range in Chart

Hello,

I'm using named dynamic ranges in a chart of mine - something I have found handy and has worked so far without any problems.

However, I've come across a strange problem whereby I have a named dynamic range that Excel claims is an invalid series when I try to plot it.

I have looked in my named ranges and whenever I highlight the series, it correctly highlights the data column range that is supposed to be plotting (so as far as I can tell, the series is valid). However, when I try to use it as a range in my chart, I keep getting the invalid message:

"A formula in this worksheet contains one or more invalid references.
Very that your formulas contain a valid path, wokrbook, range name, and cell reference."

Now I've checked the spelling, etc. of the range I'm putting into my chart's values and the name in the collection of names - and I know it's the same (done about a dozen times or so).

Any ideas what might be causing this? Could it be an Excel bug? The only think which was out of the ordinary is that I originally had the series defined one way and I later changed to read either of 2 series, depending on a user specification. The trouble series plots only zeros, but I don't see why that should be a problem.

Any advice would be much appreciated.

-Pete

Hi Andy,

Thanks for your response.

The workbook would be a bit unwieldy, however these are the formulas as I have copied them directly from the collection of names in the workbook:

1). This is the series I am having trouble with - defined for qtrs = TRUE, but not defined otherwise (for some reason):

ch1_qtrmarkval
=IF(qtrs=TRUE,ch1_qtrmarkvaltrue,ch1_qtrmarkvalfalse)

2). This is the series if qtrs = TRUE:

ch1_qtrmarkvaltrue
=INDIRECT("TimeSeriesAnalysis!$I$"&MATCH(ch_startdt,TimeSeriesAnalysis!$A:$A,0)&":"&"$I$"&MATCH(ch_e nddt,TimeSeriesAnalysis!$A:$A,0))

[correctly highlights the data range when I go to the formula in the Names box]

3). This is the series if qtrs = FALSE:
ch1_qtrmarkvalfalse
=INDIRECT("TimeSeriesAnalysis!$J$"&MATCH(ch_startdt,TimeSeriesAnalysis!$A:$A,0)&":"&"$J$"&MATCH(ch_e nddt,TimeSeriesAnalysis!$A:$A,0))

[correctly highlights the data range when I go to the formula in the Names box]

Given that both appear to be individually valid series, I cannot make out why the simple if/then statement in #1 is not resolving correctly (unless if someone can spot another mistake here somewhere, like spelling, etc.).

Any ideas would be welcome - I suspect it might be a bug rather than an error.

Hi Andy,

Thanks for that response.

I'm quite sure there is a bug involved or perhaps a corrupted workbook as I now also notice that my dropdown boxes are not accurately changing their linked cells, either.

I appreciate your attention.

-Pete