Dynamic Range Chart ???

Hi,

After looking through the group's archives, I am trying to create a
chart that reflects a "rolling" date range. In this example it is for
91 days (13 weeks).

Here is what I have:
Col A Col b COL C Col D
Date disregard disgegard data to chart


I have hundreds of dates entered and col D's data is derived from a
formula.

Here are my named ranges

"IIIMonth": =OFFSET(Log!$D$2,COUNTA(Log!$D:$D)-91,0,91)
"Date": =OFFSET(Log!$A$2,COUNTA(Log!$A:$A)-91,0,91)

They seem to work, but the chart's data for col D does not match what
is in col d. The dates match, but not the data. For example, the value
in Cell A481 is 4/23/05. That is reflected in the chart. However, the
Data in D481 is 15.07, but the charted value is 11.97.

I am baffled. Hopefully this makes sense. Any help appreciated.

Al in Nebraska

Hi,

It's hard to tell without the data as to why one of your named ranges is
working and the other not.
Instead why not use the one that is working as a references.

"Date": =OFFSET(Log!$A$2,COUNTA(Log!$A:$A)-91,0,91)
"IIIMonth": =OFFSET(Date,0,3)

Cheers
Andy

Yogalete wrote:
> Hi,
>
> After looking through the group's archives, I am trying to create a
> chart that reflects a "rolling" date range. In this example it is for
> 91 days (13 weeks).
>
> Here is what I have:
> Col A Col b COL C Col D
> Date disregard disgegard data to chart
>
>
> I have hundreds of dates entered and col D's data is derived from a
> formula.
>
> Here are my named ranges
>
> "IIIMonth": =OFFSET(Log!$D$2,COUNTA(Log!$D:$D)-91,0,91)
> "Date": =OFFSET(Log!$A$2,COUNTA(Log!$A:$A)-91,0,91)
>
> They seem to work, but the chart's data for col D does not match what
> is in col d. The dates match, but not the data. For example, the value
> in Cell A481 is 4/23/05. That is reflected in the chart. However, the
> Data in D481 is 15.07, but the charted value is 11.97.
>
> I am baffled. Hopefully this makes sense. Any help appreciated.
>
> Al in Nebraska
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Worked! Thanks Andy.

Al,

Try redefining your range names to the following:

The defined name called “Dates” refers to the dates in column A. Create a
defined range name called “Dates” and set the named formula up like this:

= OFFSET(Log!$A$1,COUNTA(Log!$A:$A)-91,0,91)

The defined name called “Data” refers to the readings in column D. Create a
defined range name called “Data” and set the named formula up like this:

= OFFSET(Dates,0,3,91)

Note the use of the first defined range name called “Dates” in the second
formula. With this setup, your rolling range keys in on the date range
first. Any data that you want to chart is then offset of the date range.
This should insure that your numbers properly sync up with your dates.

----
Regards,
John Mansfield
http://www.pdbook.com


"Yogalete" wrote:

> Hi,
>
> After looking through the group's archives, I am trying to create a
> chart that reflects a "rolling" date range. In this example it is for
> 91 days (13 weeks).
>
> Here is what I have:
> Col A Col b COL C Col D
> Date disregard disgegard data to chart
>
>
> I have hundreds of dates entered and col D's data is derived from a
> formula.
>
> Here are my named ranges
>
> "IIIMonth": =OFFSET(Log!$D$2,COUNTA(Log!$D:$D)-91,0,91)
> "Date": =OFFSET(Log!$A$2,COUNTA(Log!$A:$A)-91,0,91)
>
> They seem to work, but the chart's data for col D does not match what
> is in col d. The dates match, but not the data. For example, the value
> in Cell A481 is 4/23/05. That is reflected in the chart. However, the
> Data in D481 is 15.07, but the charted value is 11.97.
>
> I am baffled. Hopefully this makes sense. Any help appreciated.
>
> Al in Nebraska
>
>