Hi (again) -
I've been trying to figure this out for the last 10+ hours or so... but I'm at a loss. All the formulas look right to me. The XLS is now too large to upload here, even when in ZIP format.
The issue I'm having is that now any time I add a new row to any of the tables used to setup ARTs, teams, or timeboxes or any time I add a row of new velocity or business value data, I see the error in the attached pic.
Excel found a problem with one or more formula references.
I'm not sure, but I suspect this may have (must have?) been caused by the changes to the formulas that populate the charts, but they all look right to me:
| Chart_Burndown_Axis | =IF(COUNTA(ReportBurndown!$X$2:$X$2)>0,OFFSET(ReportBurndown!$W$2,,,MAX(2,COUNTA(ReportBurndown!$Y$2:$Y$9999)),2),0) |
| Chart_Burndown_Fast | =IFERROR(OFFSET(ReportBurndown!$Z$2,,,COUNTA(ReportBurndown!$Y$2:$Y$9999),1),0) |
| Chart_Burndown_Mean | =IFERROR(OFFSET(ReportBurndown!$Y$2,,,COUNTA(ReportBurndown!$Y$2:$Y$9999),1),0) |
| Chart_Burndown_Slow | =IFERROR(OFFSET(ReportBurndown!$AA$2,,,COUNTA(ReportBurndown!$Y$2:$Y$9999),1),0) |
| Chart_Confidence_Axis | =IF(COUNTA(ReportConfidence!$X$2:$X$2)>0,OFFSET(ReportConfidence!$W$2,,,MAX(2,COUNTA(ReportConfidence!$AA$2:$AA$9999)),2),0) |
| Chart_Confidence_High_End | =IFERROR(OFFSET(ReportConfidence!$Z$2,,,COUNTA(ReportConfidence!$AA$2:$AA$9999),1),0) |
| Chart_Confidence_Low_End | =IFERROR(OFFSET(ReportConfidence!$Y$2,,,COUNTA(ReportConfidence!$AA$2:$AA$9999),1),0) |
| Chart_Confidence_Mean | =IFERROR(OFFSET(ReportConfidence!$AA$2,,,COUNTA(ReportConfidence!$AA$2:$AA$9999),1),0) |
| Chart_PPM_Axis | =IF(COUNTA(ReportPPM!$Y$2:$Y$2)>0,OFFSET(ReportPPM!$W$2,,,COUNTA(ReportPPM!$AA$2:$AA$9999),2),0) |
| Chart_PPM_Green_Diff | =IFERROR(OFFSET(ReportPPM!$Y$2,,,COUNTA(ReportPPM!$AA$2:$AA$9999),1),0) |
| Chart_PPM_Green_Low | =IFERROR(OFFSET(ReportPPM!$Z$2,,,COUNTA(ReportPPM!$AA$2:$AA$9999),1),0) |
| Chart_PPM_PPM | =IFERROR(OFFSET(ReportPPM!$AA$2,,,COUNTA(ReportPPM!$AA$2:$AA$9999),1),0) |
| Chart_TPM_Axis | =IF(COUNTA(ReportTPM!$X$2:$X$2)>0,OFFSET(ReportTPM!$X$2,,,COUNTA(ReportTPM!$AB$2:$AB$9999),2),0) |
| Chart_TPM_Green_Diff | =IFERROR(OFFSET(ReportTPM!$Z$2,,,COUNTA(ReportTPM!$AB$2:$AB$9999),1),0) |
| Chart_TPM_Green_Low | =IFERROR(OFFSET(ReportTPM!$AA$2,,,COUNTA(ReportTPM!$AB$2:$AB$9999),1),0) |
| Chart_TPM_TPM | =IFERROR(OFFSET(ReportTPM!$AB$2,,,COUNTA(ReportTPM!$AB$2:$AB$9999),1),0) |
| Chart_Velocity_ART_Axis | =IF(COUNTA(ReportVelocityART!$X$2:$X$2)>0,OFFSET(ReportVelocityART!$W$2,,,COUNTA(ReportVelocityART!$Y$2:$AC$9999),3),0) |
| Chart_Velocity_ART_Differential | =IFERROR(OFFSET(ReportVelocityART!$AA$2,,,COUNTA(ReportVelocityART!$AC$2:$AC$9999),1),0) |
| Chart_Velocity_ART_Low | =IFERROR(OFFSET(ReportVelocityART!$Z$2,,,COUNTA(ReportVelocityART!$AC$2:$AC$9999),1),0) |
| Chart_Velocity_ART_Ratio_Avg | =IFERROR(OFFSET(ReportVelocityART!$AD$2,,,COUNTA(ReportVelocityART!$AC$2:$AC$9999),1),0) |
| Chart_Velocity_ART_Ratio_Max | =IFERROR(OFFSET(ReportVelocityART!$AC$2,,,COUNTA(ReportVelocityART!$AC$2:$AC$9999),1),0) |
| Chart_Velocity_ART_Ratio_Min | =IFERROR(OFFSET(ReportVelocityART!$AB$2,,,COUNTA(ReportVelocityART!$AC$2:$AC$9999),1),0) |
| Chart_Velocity_Axis | =IF(COUNTA(ReportVelocity!$X$2:$X$2)>0,OFFSET(ReportVelocity!$W$2,,,COUNTA(ReportVelocity!$Y$2:$AC$9999),4),0) |
| Chart_Velocity_Differential | =IFERROR(OFFSET(ReportVelocity!$AB$2,,,COUNTA(ReportVelocity!$AC$2:$AC$9999),1),0) |
| Chart_Velocity_Low | =IFERROR(OFFSET(ReportVelocity!$AA$2,,,COUNTA(ReportVelocity!$AC$2:$AC$9999),1),0) |
| Chart_Velocity_Ratio | =IFERROR(OFFSET(ReportVelocity!$AC$2,,,COUNTA(ReportVelocity!$AC$2:$AC$9999),1),0) |
| Droplist_ART_Teams | =Table_Teams[ART TEAM] |
| Droplist_ARTs | ='Setup ARTs'!$G$3:INDEX('Setup ARTs'!$G$3:$G$9999,COUNTIF('Setup ARTs'!$G$3:$G$9999,"?*")) |
| Droplist_ARTs_Sorted | ='Setup ARTs'!$I$3:INDEX('Setup ARTs'!$I$3:$I$9999,COUNTIF('Setup ARTs'!$I$3:$I$9999,"?*")) |
| Droplist_PIs_Sorted | ='Setup Timeboxes'!$S$3:INDEX('Setup Timeboxes'!$S$3:$S$9999,COUNTIF('Setup Timeboxes'!$S$3:$S$9999,"?*")) |
| Droplist_Sprints_Sorted | ='Setup Timeboxes'!$M$3:INDEX('Setup Timeboxes'!$M$3:$M$9999,COUNTIF('Setup Timeboxes'!$M$3:$M$9999,"?*")) |
| Droplist_Teams | ='Setup Teams'!$H$3:INDEX('Setup Teams'!$H$3:$H$9999,COUNTIF('Setup Teams'!$H$3:$H$9999,"?*")) |
| Droplist_Teams_Sorted | ='Setup Teams'!$J$3:INDEX('Setup Teams'!$J$3:$J$9999,COUNTIF('Setup Teams'!$J$3:$J$9999,"?*")) |
| Flag_Inactive_BV | =Counts!$C$22 |
| Flag_Inactive_Velocity | =Counts!$C$15 |
| Slicer_ART | =CUBESET("ThisWorkbookDataModel","{"&"[Table_BV].[ART].&[Momentum]"&"}") |
| Slicer_ART1 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[ART].&[Momentum]"&"}") |
| Slicer_ART2 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[ART].[All]"&"}") |
| Slicer_ART3 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[ART].[All]"&"}") |
| Slicer_ART4 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_BV].[ART].&[Momentum]"&"}") |
| Slicer_ART5 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[ART].&[Momentum]"&"}") |
| Slicer_PI | =CUBESET("ThisWorkbookDataModel","{"&"[Table_BV].[PI].[All]"&"}") |
| Slicer_PI1 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[PI].[All]"&"}") |
| Slicer_PI2 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[PI].&[PI-3]"&"}") |
| Slicer_PI3 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[PI].[All]"&"}") |
| Slicer_PI4 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_BV].[PI].&[PI-1]"&","&"[Table_BV].[PI].&[PI-2]"&","&"[Table_BV].[PI].&[PI-3]"&"}") |
| Slicer_PI5 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[PI].&[PI-3]"&"}") |
| Slicer_SPRINT | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[SPRINT].[All]"&"}") |
| Slicer_SPRINT1 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[SPRINT].[All]"&"}") |
| Slicer_SPRINT2 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[SPRINT].[All]"&"}") |
| Slicer_SPRINT3 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[SPRINT].[All]"&"}") |
| Slicer_STATUS | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[STATUS].&[Active]"&"}") |
| Slicer_STATUS1 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_BV].[STATUS PI].[All]"&"}") |
| Slicer_STATUS2 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[STATUS].&[Active]"&"}") |
| Slicer_STATUS3 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_BV].[STATUS].&[Active]"&"}") |
| Slicer_STATUS4 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[STATUS].[All]"&"}") |
| Slicer_STATUS5 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[STATUS].&[Active]"&"}") |
| Slicer_TEAM | =CUBESET("ThisWorkbookDataModel","{"&"[Table_BV].[TEAM].&[Thoroughbreds]"&"}") |
| Slicer_TEAM1 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[TEAM].&[Skills to Pay the Bills]"&"}") |
| Slicer_TEAM2 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[TEAM].&[Guardians of the GL]"&"}") |
| Slicer_TEAM3 | =CUBESET("ThisWorkbookDataModel","{"&"[Table_Velocity].[TEAM].&[G-Invoicing]"&"}") |
| varBurndownPoints | =ReportBurndown!$AM$5 |
| varLighthouseMaxSprints | =ReportLighthouse!$R$18 |
| varLighthouseNumSprints | =ReportLighthouse!$R$20 |
| varLighthouseProjectEndDate | =ReportLighthouse!$R$23 |
| varLighthouseS1End | =ReportLighthouse!$R$17 |
| varLighthouseS1Start | =ReportLighthouse!$R$11 |
| varLighthouseSprintDuration | =ReportLighthouse!$R$14 |
| varVelocityLowerRange | =Counts!$B$17 |
| varVelocityUpperRange | =Counts!$B$18 |
Hi Shawn,
This problem existed in your original file shared on this forum, so I don't think it's related to any recent changes to names. It also isn't specific to any particular table because the error is triggered when inserting a new row in a completely new table I added in the file.
It's often caused by defined names used in charts that return no data. If you look at the Lighthouse Report you'll see that all but the first legend entry have references to errors for their axis labels. That said, I tried deleting this chart and the error still occurs. I also tried deleting all charts and the error still occurs.
I also deleted the defined names in batches and the error still persists.
I also checked all chart references and they all return data, so I'm at a loss as to what is causing it. Maybe Velouria has some ideas.
Mynda
Well, first of... wow. Even though the problem remains, I certainly appreciate the effort you put into hunting it down. I tried some of the same things with the same result. It's vexxing.
It appears to be down to any chart that has no data - in your original sample file, the Velocity chart had all data filtered out. A simple fix is to amend the formulas to always return a range - eg:
=IF(COUNTA(ReportVelocity!$X$2:$X$2)>0, OFFSET(ReportVelocity!$W$2,,,COUNTA(ReportVelocity!$Y$2:$AC$9999),4),ReportVelocity!$W$2)
rather than
=IF(COUNTA(ReportVelocity!$X$2:$X$2)>0, OFFSET(ReportVelocity!$W$2,,,COUNTA(ReportVelocity!$Y$2:$AC$9999),4),0)
and similarly with the other names for the chart.
Just want to keep this thread alive. I made the recommended changes but am still seeing the error when I add a row to ANY of the tables (ART, Team, Timebox, Velocity, BV). I checked every formula, every chart, every conditional formatting setting, etc., but no joy yet. Still checking. It's such a vague error; you'd think Excel could be more specific about what's triggering the error.
