Expanding and Contracting chart range doesn't save in Excel 2007

[Shortmeister1]

Hi

I've hit a strange problem that I think should work, but doesn't.

My chart data range is: ='Summary Data'!$A$2:$BF$8.

Since it expands a few times a week to BG, BH, BI etc., it seemed to make sense to use the standard OFFSET and COUNTA formulae to expand it. The every time I typed the Offset formula in, it worked but each time I save it, it changes to an updated (correct) fixed reference. i.e. back to the format above.

Am I doing something wrong? Many threads I've read seem to say that you should be able to use an expanding range in charting.

Thanks
Martin

 

[Derek Brown]

Perhaps this is the answer that you are looking for (not the VBA but the use of the workbook name).
Dynamic named range used in Chart Data range reverts to static value

 

[Derek Brown]

This somehow got posted twice.

 

[Shortmeister1]

Thanks for responding - appreciated.

Hmmm. Exactly the same problem.

I think I'm just going to have to write some code. Weird though. It shouldn't do this.

Cheers
Martin

 

[Shortmeister1]

Hi Derek

Thanks - you started me on a voyage of discovery.

Solution? - No VBA*.
No Dynamic Offsets for Dynamic Named Ranges.

I used the "Table" functionality introduced to Excel 2007. It integrates perfectly with the Chart dialog boxes.

*I did actually use one bit of code as a one off. This was to name the table to something a little bit more meaningful for me.

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$5"), , xlYes).Name = "tblSummary"

Cheers
Martin