Excel 2007 - External Datasources (Tables) Changing Absolute Ranges

Question

Excel 2007 is driving me nuts. I have several charts set up that I refresh periodically against some data in a SQL database. However, every time I refresh them, Excel bumps any absolute ranges I have for the charts to view this data by the amount of new records returned from the query.

The data is time related (I have a record for each minute of the day), and I want the chart (line chart) to be fixed in size... instead, Excel wants it to dynamically change, and thus it adds the extra rows to my named ranges.

Is there a way to prevent Excel from updating absolute named ranges when new data from a data source is returned? I hope this makes sense.

Answer 1

How are you defining your named ranges? I'm assuming your are just using cell references, if so it may be worth looking at the offset function, particularly if you are dealing with fixed size ranges (e.g. minutes in a day).

For example, if you had column headers in the first row, your minutes were in column A and you had exactly 1 row for each minute your offset formula would look like:

OFFSET(A2,0,0,1,1440)

and is defined as

OFFSET(StartCell, OffsetRows, OffsetCols, Width, Height)

Because any of the values within the arguments can also be formulas, OFFSET is a very useful function in defining ranges.