Using a named range in a chart series formula

[MCCCLXXXV]

Hi all,

I've defined cell F5 to give me an address based on some user input from other cells. Specifically, F5 = ADDRESS(MATCH(F1,$A:$A,0),2) & ":" & =ADDRESS(MATCH(F2,$A:$A,0),2).

So F5 returns something like $B$100:$B$200

I've created a named range called ISM that is refers to =INDIRECT(Sheet1!$F$5)

I know that this named range works because I can type =SUM(ISM) in another cell and it gives me the proper sum even when the user changes the values in cells F1 and F2.

However, when I try to put Sheet1!ISM into the series function of a chart, I get an error:
"A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference."

Any idea why this happens? According to this microsoft support article (towards the bottom), it says the named range should support the use of the "indirect" function.

Thanks,
Joe

[sailepaty]

What happen if you define your ISM range like this?

=INDEX($B:$B,MATCH(F1,$A:$A,0)) : INDEX($B:$B,MATCH(F2,$A:$A,0))

Regards

[MCCCLXXXV]

Hi sailepaty,

Thanks for your solution, but it doesn't seem to work. The =SUM(ISM) function I referred to in my first post gives me a #VALUE error when I use your version of the named range and the Chart series application gives me the same error. Also, I assumed you meant the following:

=INDEX($B:$B,MATCH(F1,$A:$A,0))&":"&INDEX($B:$B,MATCH(F2,$A:$A,0))

(with the &s ...)

[sailepaty]

Sorry I missed to set F1 and F2 absolute.

=INDEX($B:$B,MATCH($F$1,$A:$A,0)) : INDEX($B:$B,MATCH($F$2,$A:$A,0))

Regards

[MCCCLXXXV]

That didn't seem to help either...

[sailepaty]

What didn't work? The definition of the dinamic range or the use of it on the chart?

[scottylad2]

Try typing the same name your trying to add to the chart into a cell with the = sign in front, hit F2 and then press F9 to see what the reference shows up. If no joy upload a sample please

[sailepaty]

That didn't seem to help either...

See the attachment.

Chart with dinamic range.zip

[MCCCLXXXV]

Hi guys,

I've attached what I'm trying to do here. Cells F3 and F4 can be changed to any date so that the range in F5 changes to correspond with the selected dates. The "ISM" named range refers to =INDIRECT(Sheet1!$F$5).

In the chart, I want to change the SERIES function from:

=SERIES(,Sheet1!$B$18:$B$784,Sheet1!$C$18:$C$784,1)

to

=SERIES(,Sheet1!ISM,Sheet1!$C$18:$C$784,1)

But excel doesn't let me. I've tried the suggestions here and can't seem to get it to work. (Also, sailepaty, I can't find your attachment on the page.. I've never downloaded one from this forum before, so maybe I'm just missing it...)

Scottylad, I tried your suggestion and pressing F9 shows the exact array I'd like to plot, so I'm not sure why the chart won't accept it.

Thanks again!

SampleNamedRange.xlsx

[sailepaty]

Sorry about the previous attachment, it's there now. Also, here is your file with the dinamic range on the chart.

SampleNamedRange.xlsx


Regards

[MCCCLXXXV]

Great! That works perfectly! Do you know why my attempt (using INDIRECT) didn't work?