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





"jimfrog" wrote in message
...
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





"jimfrog" wrote in message
...
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?