Named Ranges in chart creating Invalid Reference

[cubsfan05]

I've used named ranges to create charts before but lately I seem to be having some trouble with it as i've got a couple named ranges (one for each series pretty much) which relate to the proper data but when I put the named ranges into the chart, I get the error message "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell refrence."

I know the error is within the chart because i cease getting this error if I remove the chart.
If someone could help with this, It'd be greatly appreciated as I like to create a lot of dynamic charts like this.

See link below for specific file
https://my.syncplicity.com/share/4bvbironljnggdz/Oil%20_%20Gas%20Charting%20Help

Running Microsoft Office Professional Plus 2010 on PC
Happy to answer any questions

Thanks in advance!!

 

[Steve=True]

Hi Cubsfan05,

You can get rid of the error from popping up by removing the invalid reference in the Horizontal (Category) Axis Labels in your chart.

Your Formulas>Defined Names>Name Manager>Dates is the invalid reference.

You need to fix that offset formula, and then reset the the Horizontal (Category) Axis Labels in your chart.

Hope that helps.

Steve=True

 

[cubsfan05]

Thanks for the reply, I corrected the named range formulas in the linked file below, however, I'm still receiving the same error. I believe it has to do with how the names are entered into the select data window on the chart, but I can't get it to work. I had previously typed in the series name as ='[filename]'!OilPrices

https://my.syncplicity.com/share/4ghyi2uk6hujip6/Copy of Oil _ Gas Charting Template v2

Thanks again in advance.

 

[63falcondude]

You can type anything into the series name. In the select data source window, make sure you are using the correct range of data.

 

[cubsfan05]

I'm unable to enter ='Copy of Oil &'Price Volume'!$9:$9 Gas Charting Template v2.xlsx'!Oil_Price in the select data window as it produces that error mentioned previously

 

[63falcondude]

The two things that you enter in the select data window are the name of the data (example: month) and the range of the data (ex:A1:A12)

 

[63falcondude]

Try typing the name of the cell that the data is in (example: A1) instead of clicking on it.