static data series height after data insertion

  • Thread starter Thread starter trizub
  • Start date Start date
T

trizub

How do I add a cell to a data series without changing the range of the
data series? I have a line chart with a line that represents only 30
days of data (the chart data series is defined to be exactly 30 cells
high). When I update the data with today's data by inserting today's
date and data to the top of the data series range, the data series
definition in the charts changes to 31 days high. I need it to be
only 30 days high after the insert. I don't want to change or write a
macro to change the data series definition in the chart. I tried
naming the range and using the name in the data series, but after
doing the update/insert, the range grows to 31 days.
 
You can use a dynamic range name for your data.

Create a range named "data' with the following in the "refers to"
block

=OFFSET(Sheet1!$B$1,0,0,30,1)

where your data would be in the first 30 rows of column B. Then
assign the range "data" to the "series values" of the appropriate data
series. The sheet name needs to be included in the "series values"
block (ie ='sheet1'!data").

Good luck.

Ken
Norfolk, Va
 
Back
Top