Defined Name as Data Series in Excel 2007 Chart
Posted on 2011-05-11
I recently upgraded to Office 2007 from Office 2003 and have been experiencing a number of challenges with migrating macros/formats/etc... One issue that I have not been able to find a solution for is using a defined name as a data series in a chart while utilizing the Address/Match/Indirect functions.
The formula below is one, among many, that I had defined in 2003 and worked like a charm prior to the upgrade.
=OFFSET(INDIRECT("'RecessionDaily'!"&ADDRESS(MATCH('DATA_DATASOURCE-DAILY'!$A$1,RecessionDaily!$A:$A,FALSE), 1)), 0, 1, 5037)
Which I reference in the "Select Data Series" box of my charts as '=My_Workbook.xlsm!Recession_NamedRange'
Now I get an error message stating that the "Formula contains one or more invalid references..."
My workbook contains 75+ charts that have referenced a number of different defined names as data series. The formula returns data when I test in a worksheet and use as in an array.
Any help would be much appreciated.