• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • 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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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