Solved

# Defined Name as Data Series in Excel 2007 Chart

Posted on 2011-05-11
206 Views
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.

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
Question by:donisanp

LVL 85

Expert Comment

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

Author Comment

No dice -- still getting the same error message.
0

LVL 85

Expert Comment

=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

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.
0

LVL 85

Accepted Solution

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

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)
0

Author Closing Comment

Thanks for the help!
0

## Featured Post

### Suggested Solutions

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.