Series Named Range Problem

[Arturo]

I have a chart built correctly and have set all the components to named
ranges I intend to adjust via code based on differing record counts filling
the sheet. The code will adjust the size of these named ranges based on the
number of those records.

Here’s Series1 cell references;
=SERIES(Sheet5!$C$5,Sheet5!$B$6:$B$20,Sheet5!$C$6:$C$20,1)

When I select this first series and alter it as follows, I get a warning:
“A formula in this workbook contains one or more invalid references. Verify
that your formal contains a valid path, workbook, range name and cell
reference.
=SERIES(Field_StartDate,XAxis_Lables,Values_StartDate,1)

When I preface each named range Sheet5! The formula works but results in this:
=SERIES('Gantt Layout 2007.07.03.xls'!Field_StartDate,'Gantt Layout
2007.07.03.xls'!XAxis_Lables,'Gantt Layout 2007.07.03.xls'!Values_StartDate,1)

The goal I am trying to achieve is a cleaner Series formula not a messier one…

I am perplexed as to why this approach is proving problematic and how to
resolve this.

Any advice welcome…

Appreciatively,
Arturo

 

[Jon Peltier]

You can't make a formula cleaner if it makes the formula invalid. I can't
tidy up 1+1=2 by removing that awkward plus sign, can I? If the Name is
defined for the workbook at large, its official name has the workbook name
attached, as Excel rewrites it for you. Fortunately Excel allows you to
enter it with the worksheet name instead. But you need some reference to a
sheet or workbook where the Name is located.

- Jon