Dynamic chart for time period

Hi All, ## See attached for example data ##

I have a dynamic chart going across rows in Excel 2007

Row 1 Dates
Row 2 Data
Row 3 Data

Using the following formula I get my dynamic chart

OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B$3:$B$3),COUNTA(Sheet1!$3:$3))


I'm attempting to update this to only include the last 6 Months of data using the following,

OFFSET(Sheet2!$A$2,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$2:$2)-1,0,-MIN(TimeFrame,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$2:$2)-1,0))

TimeFrame is a named cell that has a number 6 in


I found the following on the web for going down a column and it works great. But I can't convert it to go along a row,

OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)


Please help

Many Thanks

Errorfree, welcome to the forum

without seeing your data it is very hard to tell what goes wrong. Please post a sample file with the data and the chart.

Hello errorfree,

change the definition of the range names to

DataDate =OFFSET(Data!$B$1,0,COUNTA(Data!$1:$1)-7,1,TimeFrame)
DataVal1 =OFFSET(DataDate,1,0)
DataVal2 =OFFSET(DataDate,2,0)

Next, select the blue series and change the Series formula in the formula bar to

=SERIES(Data!$A$2,'Book2-1.xls'!DataDate,'Book2-1.xls'!DataVal1,2)

Then select the red series and change the formula to

=SERIES(Data!$A$2,'Book2-1.xls'!DataDate,'Book2-1.xls'!DataVal2,1)

see attached.

cheers

errorfree, before you post again in this forum, please have a good read of the forum rules and follow the cross posting link in my signature.

Then add links to all posts in other forums where you posted the same question, and vice versa.

None of us likes to waste time on a question that has been answered elsewhere.