Named Ranges in Series Formula

[Ed Burke]

Hello again all,

Unable to find the answer via search...

I've created a bullet chart to visualize the information I need for my current project. I'm working to extend the functionality by replacing the fixed references with named ranges, but Excel won't accept it.

The named ranges I created use OFFSETs. I've tested these formulae and they work fine, always returning the range I expect. However, when I edit the chart's data set (or attempt to edit the SERIES formula directly), I get the following error: "We found a problem with one or more formula references in this worksheet." It does not highlight the problem area, but I only receive this error after adding a named range to the SERIES formula.

I've attempted to use INDIRECT, but this also fails. I've also attempted to modify the name definition to include fixed references only, but the formula still won't accept the named range. The named ranges are defined at the workbook level and reference table column (structured references); however I don't believe this to be a problem since it returns the correct range when used independently and I still get the error when using fixed references (R1C1).

I've also attempted to use the workbook name in the SERIES formula to explicitly reference the named range (i.e. 'workbookname.xlsm'!namedrange). This didn't throw an error, but Excel would not accept it as part of the formula.

One example of the named range definition:

=OFFSET(_chFundsStatus[[#Headers],[Budgeted]],1,0,SeriesCount,1)

The structured table reference refers to the first row of a specific column that I want to use as the base of the series. "SeriesCount" refers to a Named Range that returns a number to determine the appropriate number of rows to include in the series. Again, this definition returns exactly the range I expect it to.

Any idea why I can't used named ranges in the SERIES formula?

Thanks in advance,
Ed

 

[NARAYANK991]

Hi ,

Can you upload your workbook ?

Narayan

 

[Ed Burke]

So I've had an interesting developing in solving this issue. I was never successfully able to directly type my named ranges into the SERIES formula, no matter how explicit the reference was. However, when selecting a series on the chart, right clicking, and clicking on Select Data..., I was able to enter an explicit reference to the name range in the following format:

'workbookname.xlsm'!rangename

Excel accepted the format. Upon returning to the chart and selecting the data series, I was surprised to discover that the SERIES formula now contained my named ranges; however, the named ranges in the SERIES formula did not have single quotation marks in them (workbookname!rangename). Odd thing is that when I go back and type the named ranges into the SERIES formula, it still rejects them. One more Excel quirk, I guess.

 

[Chihiro]

What is the scope of your named range? If it is Workbook, then you'd need to prefix with workbook name.

Ex: =SERIES(Sheet1!$B$1,Book1!NamedRange1,Book1!NamedRange2,1)

 

[Ed Burke]

All of the named ranges are scoped to the workbook. I knew of the explicit referencing, I just found it odd that I couldn't type the explicit reference directly into the SERIES formula and had to do it through the "Select Data..." dialog. Maybe something specific to my installation.

 

[Chihiro]

Maybe something specific to my installation.

Likely, tested on couple of different installations and all accepted direct imput in SERIES formula.

Win 7, Excel 2010 & Excel 2016
Win 10, Excel 2013

All 64bit Win, 32bit Excel.

 

[Ed Burke]

Thanks! I'll pass this off to IT. I'm sure it'll be really high on their priority list.