Hi all,

Just upgraded from Office 97 to Office 2007 (big jump!) and trying to fix
the things that broke!

I routinely make so-called "dynamic" charts in the traditional manner using
ranges defined by formula which resolve to ranges of cells e.g. define 'Bias
& Precision'!Samples as
=OFFSET('Bias & Precision'!$A$2,0,0,COUNTA('Bias & Precision'!$A:$A)-1)
I've had a couple of snags with these but it seems to still work as long as
I watch the scope of the names.

What seems to be very "broken" is the use of names which do not resolve to
ranges of cells. These are referred to in the Excel 2007 help (Use names to
clarify formulas) as 'Formula' type names where the examples given (which
resolve to a single number) are
=SUM(VLOOKUP(A1,B1:F20,5,FALSE), -G5) and
=SUM(Inventory_Level,-Order_Amt)

In the past I have used names which resolve to arrays, e.g. define 'Bias &
Precision'!Deviations as
='Bias & Precision'!Samples - 'Bias & Precision'!Means
and used these both as inputs to other formulae and as the sources of
charts. Because these do not exist on the worksheet I do not have to enter
the formula in a predefined range of cells, the "virtual ranges" extend as
far as needed. In Excel 2007 they still work for calculations, but not
(reliably) as sources of charts.

I get all sorts of wierd behaviour, sometimes it works sometimes not and I
haven't yet fully defined what breaks it. If I remove all the data so the
ranges are empty sets I get "A formula in this worksheet contains one or
more invalid references". If I then enter data the chart doesn't
automatically update. If I go to edit the source (using the SERIES function,
is there any other way in 2007 to get to the data series?) sometimes it
works, sometimes it won't even let me make a change - when I press the
return key nothing happens!

I'm aware of 'tables' which would meet my needs in many ways BUT I believe
you cannot extend the range of tables in protected sheets and I do need to
protect my sheets; I'm producing tools for users who are fully capable of
accidentally overwriting my formulae!

I'm working around it by putting the formulae into (longer than I anticipate
will be needed) cell ranges which is really better in some ways anyway;
these "virtual ranges" are a bit opaque, perhaps too clever). Nevertheless
I'd be interested to know whether the Excel 2007 behaviour is a recognised
"improvement", whether it is likely to remain, and if anyone has
characterised it any better than me.

Best regards,

A Lurker
(Sorry to be anonymous, but needs must)

I too have run into this same problem.....unfortunately, I have not been able
to find a solution yet. I am using dynamic ranges for a couple of charts
(using offset formulas), but when I open the workbook in Excel 2007, I get
the "A formula in this worksheet contains one or more invalid references"
message. This is maddening because I don't know of any other way to
accomplish having a dynamic chart. I'm assuming this is an Excel 2007 bug,
so I downloaded the Office 2007 Service Pack 1 today in hopes that it was
fixed. Unfortunately I'm still getting the same message.

If anybody knows of a fix for this, or a workaround, I'd appreciate hearing
about it.

Jeff

"Lurker" wrote:

Hi all,

Just upgraded from Office 97 to Office 2007 (big jump!) and trying to fix
the things that broke!

I routinely make so-called "dynamic" charts in the traditional manner using
ranges defined by formula which resolve to ranges of cells e.g. define 'Bias
& Precision'!Samples as
=OFFSET('Bias & Precision'!$A$2,0,0,COUNTA('Bias & Precision'!$A:$A)-1)
I've had a couple of snags with these but it seems to still work as long as
I watch the scope of the names.

What seems to be very "broken" is the use of names which do not resolve to
ranges of cells. These are referred to in the Excel 2007 help (Use names to
clarify formulas) as 'Formula' type names where the examples given (which
resolve to a single number) are
=SUM(VLOOKUP(A1,B1:F20,5,FALSE), -G5) and
=SUM(Inventory_Level,-Order_Amt)

In the past I have used names which resolve to arrays, e.g. define 'Bias &
Precision'!Deviations as
='Bias & Precision'!Samples - 'Bias & Precision'!Means
and used these both as inputs to other formulae and as the sources of
charts. Because these do not exist on the worksheet I do not have to enter
the formula in a predefined range of cells, the "virtual ranges" extend as
far as needed. In Excel 2007 they still work for calculations, but not
(reliably) as sources of charts.

I get all sorts of wierd behaviour, sometimes it works sometimes not and I
haven't yet fully defined what breaks it. If I remove all the data so the
ranges are empty sets I get "A formula in this worksheet contains one or
more invalid references". If I then enter data the chart doesn't
automatically update. If I go to edit the source (using the SERIES function,
is there any other way in 2007 to get to the data series?) sometimes it
works, sometimes it won't even let me make a change - when I press the
return key nothing happens!

I'm aware of 'tables' which would meet my needs in many ways BUT I believe
you cannot extend the range of tables in protected sheets and I do need to
protect my sheets; I'm producing tools for users who are fully capable of
accidentally overwriting my formulae!

I'm working around it by putting the formulae into (longer than I anticipate
will be needed) cell ranges which is really better in some ways anyway;
these "virtual ranges" are a bit opaque, perhaps too clever). Nevertheless
I'd be interested to know whether the Excel 2007 behaviour is a recognised
"improvement", whether it is likely to remain, and if anyone has
characterised it any better than me.

Best regards,

A Lurker
(Sorry to be anonymous, but needs must)

I find the offset technique for dynamic charts works as long as
a) the scope of the names,
b) the ranges of cells they refer to, and
c) the chart
are all on the same worksheet. This is a big restriction.

I still welcome any clarification from anyone else.
Please let us know if Service Pack 1 helps.

HTH

A Lurker

"goofy11" wrote in message
...
I too have run into this same problem.....unfortunately, I have not been
able
to find a solution yet. I am using dynamic ranges for a couple of charts
(using offset formulas), but when I open the workbook in Excel 2007, I get
the "A formula in this worksheet contains one or more invalid references"
message. This is maddening because I don't know of any other way to
accomplish having a dynamic chart. I'm assuming this is an Excel 2007
bug,
so I downloaded the Office 2007 Service Pack 1 today in hopes that it was
fixed. Unfortunately I'm still getting the same message.

If anybody knows of a fix for this, or a workaround, I'd appreciate
hearing
about it.

Jeff

"Lurker" wrote:

Hi all,

Just upgraded from Office 97 to Office 2007 (big jump!) and trying to fix
the things that broke!

I routinely make so-called "dynamic" charts in the traditional manner
using
ranges defined by formula which resolve to ranges of cells e.g. define
'Bias
& Precision'!Samples as
=OFFSET('Bias & Precision'!$A$2,0,0,COUNTA('Bias & Precision'!$A:$A)-1)
I've had a couple of snags with these but it seems to still work as long
as
I watch the scope of the names.

What seems to be very "broken" is the use of names which do not resolve
to
ranges of cells. These are referred to in the Excel 2007 help (Use names
to
clarify formulas) as 'Formula' type names where the examples given (which
resolve to a single number) are
=SUM(VLOOKUP(A1,B1:F20,5,FALSE), -G5) and
=SUM(Inventory_Level,-Order_Amt)

In the past I have used names which resolve to arrays, e.g. define 'Bias
&
Precision'!Deviations as
='Bias & Precision'!Samples - 'Bias & Precision'!Means
and used these both as inputs to other formulae and as the sources of
charts. Because these do not exist on the worksheet I do not have to
enter
the formula in a predefined range of cells, the "virtual ranges" extend
as
far as needed. In Excel 2007 they still work for calculations, but not
(reliably) as sources of charts.

I get all sorts of wierd behaviour, sometimes it works sometimes not and
I
haven't yet fully defined what breaks it. If I remove all the data so the
ranges are empty sets I get "A formula in this worksheet contains one or
more invalid references". If I then enter data the chart doesn't
automatically update. If I go to edit the source (using the SERIES
function,
is there any other way in 2007 to get to the data series?) sometimes it
works, sometimes it won't even let me make a change - when I press the
return key nothing happens!

I'm aware of 'tables' which would meet my needs in many ways BUT I
believe
you cannot extend the range of tables in protected sheets and I do need
to
protect my sheets; I'm producing tools for users who are fully capable of
accidentally overwriting my formulae!

I'm working around it by putting the formulae into (longer than I
anticipate
will be needed) cell ranges which is really better in some ways anyway;
these "virtual ranges" are a bit opaque, perhaps too clever).
Nevertheless
I'd be interested to know whether the Excel 2007 behaviour is a
recognised
"improvement", whether it is likely to remain, and if anyone has
characterised it any better than me.

Best regards,

A Lurker
(Sorry to be anonymous, but needs must)