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)