Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Defined Name as Data Series in Excel 2007 Chart

Experts:

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.

0
donisanp
Asked:
donisanp
  • 4
  • 3
1 Solution
 
Rory ArchibaldCommented:
Does:
=OFFSET(INDEX('RecessionDaily'!A:A,MATCH('DATA_DATASOURCE-DAILY'!$A$1,RecessionDaily!$A:$A,0)), 0, 1, 5037,1)
work any better?
0
 
donisanpAuthor Commented:
No dice -- still getting the same error message.
0
 
Rory ArchibaldCommented:
How about:
=INDEX('RecessionDaily'!A:A,MATCH('DATA_DATASOURCE-DAILY'!$A$1,RecessionDaily!$A:$A,0)):INDEX('RecessionDaily'!A:A,MATCH('DATA_DATASOURCE-DAILY'!$A$1,RecessionDaily!$A:$A,0)+5036)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
donisanpAuthor Commented:
This technically works, but the purpose of the offset is to reference a date in column A and return the the value in column B for the same row.
0
 
Rory ArchibaldCommented:
Small tweak then:
=INDEX('RecessionDaily'!B:B,MATCH('DATA_DATASOURCE-DAILY'!$A$1,RecessionDaily!$A:$A,0)):INDEX('RecessionDaily'!B:B,MATCH('DATA_DATASOURCE-DAILY'!$A$1,RecessionDaily!$A:$A
0
 
donisanpAuthor Commented:
This works! A few tweaks to constrain this formula within 5037 rows:

=INDEX('D_RecessionBars-D'!$A:$B,MATCH('DATA_DATASOURCE-DAILY'!$A$1,'D_RecessionBars-D'!$A:$A,0), 2):
INDEX('D_RecessionBars-D'!$A:$B,MATCH('DATA_DATASOURCE-DAILY'!$A$1,'D_RecessionBars-D'!$A:$A,0)+5037, 2)
0
 
donisanpAuthor Commented:
Thanks for the help!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now