Defined Name as Data Series in Excel 2007 Chart

Posted on 2011-05-11
Last Modified: 2012-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.

Question by:donisanp
    LVL 85

    Expert Comment

    by:Rory Archibald
    =OFFSET(INDEX('RecessionDaily'!A:A,MATCH('DATA_DATASOURCE-DAILY'!$A$1,RecessionDaily!$A:$A,0)), 0, 1, 5037,1)
    work any better?

    Author Comment

    No dice -- still getting the same error message.
    LVL 85

    Expert Comment

    by:Rory Archibald
    How about:

    Author Comment

    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.
    LVL 85

    Accepted Solution

    Small tweak then:

    Author Comment

    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)

    Author Closing Comment

    Thanks for the help!

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Advanced finding in Excel 9 26
    Posting V12 2 17
    Excel - text to column problem 2 20
    EXCEL - Sumproduct using AND statement? 6 26
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now