Hi Everyone
I have a table with Dates along the top (Row 1) and in the left column (Column C) I have items which require data to be entered for that timepoint, e.g. Number of missing pages, Number of Missing Visits.
I would like to display this information as a line graph, but only displaying the last 6 records entered.
The formula which I am using for Y axis is: (Number of missing pages data)
=IFERROR(OFFSET(Overview!$D$23,,IF(COUNTA(Overview!$D$23:INDEX(Overview!$23:$23, MATCH(Status_Date, Overview!$1:$1, 0)))<6,0,COUNTA(Overview!$D$23:INDEX(Overview!$23:$23, MATCH(Status_Date, Overview!$1:$1, 0)))-6),,IF(COUNTA(Overview!$D$23:INDEX(Overview!$23:$23, MATCH(Status_Date, Overview!$1:$1, 0)))<6,COUNTA(Overview!$D$23:INDEX(Overview!$23:$23, MATCH(Status_Date, Overview!$1:$1, 0))),6)),"")
The formula which I am using for X axis is: (Date headers)
=IFERROR(OFFSET(Overview!$D$1,,IF(COUNTA(Overview!$D$1:INDEX(Overview!$1:$1, MATCH(Status_Date, Overview!$1:$1, 0)))<6,0,COUNTA(Overview!$D$1:INDEX(Overview!$1:$1, MATCH(Status_Date, Overview!$1:$1, 0)))-6),,IF(COUNTA(Overview!$D$1:INDEX(Overview!$1:$1, MATCH(Status_Date, Overview!$1:$1, 0)))<6,COUNTA(Overview!$D$1:INDEX(Overview!$1:$1, MATCH(Status_Date, Overview!$1:$1, 0))),6)),"")
Overview=Name of worksheet
Status_Date=this is Cell C2 with a dropdown list of all the dates in Row 1. The user can select any date to see the metrics from that timepoint.
If the user selects June, then the last 6 months of metrics (e.g. Number of missing pages) will be displayed in the graph to help with trending. If March is selected, then only the last 3 months will display.
This formula does work and displays up to the last 6 records as expected. I have saved this in the Name Manager function and the graph is referencing this defined name.
When I delete the dummy data to create a blank template, I am getting an error message "Error found a problem with one or more formula references in the worksheet. Check that the cell references, ranges names, defined names and links to other workbooks in your formulas are all correct."
I am not sure if my formulae is unstable or why such errors are flagging. Would anyone know what the issue would be?
Thank you in advance
Bookmarks