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
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