Chart data series and structured references (2007)

Hello Everyone,

I have put all my data in a lovely data table, allowing me to refer to my data, from anywhere in my spreadsheet, using intuitive structured reference formulas.

If you don't know what I mean by structured reference formulas, I mean formulas like this: =SUMIFS(Table[Price],Table[Item],"=Chair",Table[Terms],"=Cash")

Now, it seems to me, that when I want to chart data in my data table, I should be able to use the same structured reference terminology to define the data series for my chart, such as the x and y series for a scatter chart.

Interestingly, if I define my x series values as:

='Sheet With Table'!Table[Price]

it will accept this input, allow it to be saved, and the chart will be correct!

But, subsequently, if I re-examine how the x series is defined it will say:

='Sheet With Table'!$K$5:$K$244

It will have changed the definition "behind my back"!

But, more seriously, when it changes the definition I loose confidence that the data series is indeed defined as a dynamic range (as it had been using the structured reference).

I could, of course, abandon structured references, and define each of the columns in my data table as separate OFFSET-style dynamic ranges -- but that just seems so bloody stupid! I have *already* named all the columns in my data table, and to create OFFSET-style dynamic ranges, I would have to refer to each column in my data table using a new and different, alternative-name (why? because dynamic ranges apparently can't share same names as column titles in a data table!)

I must be missing something... but what?

Cheers,

Jay

I can see that the resolution of the structure reference can be disconcerting. but do you have any example where the chart based on a table loses sync with the table?

In my brief testing the range references, although appearing fixed, do update to match table.