I am trying to make a chart that updates automatically and only displays the
last 30 days of information starting at the current date. The data sheet for
the chart is updated automatically, so there are future dates in the column.
I hav tried the OFFSET and COUNTA route, but that fuction grabs data from the
bottom of the data. I want the chart to start at the current date and go
back 30 days.
Can Anyone Help Me?

Jim:

Apparently your date column has future dates. This prevents you from using a
simple offset function going back 30 days from last entry in column.

I personally don't like to have future dates in my books. Is there any way
you can change this? If not, here's a workaround I use.

I set up a simple sheet with dates in Col A and values in Col B.

I placed current date in Cell D2.
I calculated end row as : G2 = Match(D2,A:A,)
I calculated start row as: G1 = G2 - 30


Once I know the current Row, I defined my DT range with an Offset refers to:

= Offset(Sheet1!$A$2,Sheet1!$G$1-1,0,Sheet1$G$2-Sheet1!$G$1).

I then set my Values refers to

= Offset(Dt,0,1)

I made my chart, then substituted the Dt and values range names for the cell
references.

The trick is dynamic range name based on start and end rows.

....Kelly

(E-Mail Removed)



"jimfrog" <(E-Mail Removed)> wrote in message
news42A5572-D994-48AA-B068-(E-Mail Removed)...
>I am trying to make a chart that updates automatically and only displays
>the
> last 30 days of information starting at the current date. The data sheet
> for
> the chart is updated automatically, so there are future dates in the
> column.
> I hav tried the OFFSET and COUNTA route, but that fuction grabs data from
> the
> bottom of the data. I want the chart to start at the current date and go
> back 30 days.
> Can Anyone Help Me?

I found another way of doing it.
Instead of calculating start and end rows, I used:
=RANK(TODAY(),A2:A294,1)-29 and a length of 30 days as inputs into the OFFSET
function to get me what I wanted.

"Kelly O'Day" wrote:

> Jim:
>
> Apparently your date column has future dates. This prevents you from using a
> simple offset function going back 30 days from last entry in column.
>
> I personally don't like to have future dates in my books. Is there any way
> you can change this? If not, here's a workaround I use.
>
> I set up a simple sheet with dates in Col A and values in Col B.
>
> I placed current date in Cell D2.
> I calculated end row as : G2 = Match(D2,A:A,)
> I calculated start row as: G1 = G2 - 30
>
>
> Once I know the current Row, I defined my DT range with an Offset refers to:
>
> = Offset(Sheet1!$A$2,Sheet1!$G$1-1,0,Sheet1$G$2-Sheet1!$G$1).
>
> I then set my Values refers to
>
> = Offset(Dt,0,1)
>
> I made my chart, then substituted the Dt and values range names for the cell
> references.
>
> The trick is dynamic range name based on start and end rows.
>
> ....Kelly
>
> (E-Mail Removed)
>
>
>
> "jimfrog" <(E-Mail Removed)> wrote in message
> news42A5572-D994-48AA-B068-(E-Mail Removed)...
> >I am trying to make a chart that updates automatically and only displays
> >the
> > last 30 days of information starting at the current date. The data sheet
> > for
> > the chart is updated automatically, so there are future dates in the
> > column.
> > I hav tried the OFFSET and COUNTA route, but that fuction grabs data from
> > the
> > bottom of the data. I want the chart to start at the current date and go
> > back 30 days.
> > Can Anyone Help Me?
>
>
>