Only chart last few numbers in table

[markymark]

I have an issue with a worksheet containing a table of production records in excel 2003. We have recorded production over 3 months so there is a large amount of production data about 100+ lines, when these are displayed on a bar chart it looks messy.


I need a formula to only produce a chart with the last week or fortnight's figures. I need the spreadsheet to do this automatically without my input or maintenance.I have tried the formulas below that I found on a web site but when entered onto the worksheet I get #VALUE! and #Name? in the cells with the formulas and an error message.


These are the formulas I've tried using


XVALUES(Defined Name)

=OFFSET('Data Table'!$A$2,COUNTA('Data Table'!$B:$B)-'Data Table'!$AS$1,0,'Data Table'!$AS$1,1)


YVALUES(Defined Name)

=OFFSET('Data Tables'!XValues,0,1)


Column A is the date, column B production, cell AS1 is where I have entered 10 as the number of records to display


Can anyone shed any light on where I am going wrong? I have 6 machines that I am collecting data for so any help would be greatly appreciated


Thanks

M&M

 

[keymaster]

OFFSET returns an array. So if you enter the formula or name pointing to it in a cell, getting an error is natural as you are asking excel to show an entire list in one cell. But the named range should work fine as input data source for a chart.


If you just want to see what is in the range, enter the name =XVALUES and then press CTRL+SHIFT+Enter.


Now, coming to the #NAME! error, I am not sure, but it could be due to the incorrect capitalization (your name is XVALUES, where the formula uses XValues).

 

[keymaster]

Also, check out these 2 posts for some more ideas and implementations:


http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


http://chandoo.org/wp/2009/11/12/topx-chart/