Trouble with Dynamic Ranges and Dynamic Charts

  • I've created a workbook with a number of worksheets with charts on them. I've created named ranges for both the x-axis and y-axis for each chart, using the same formula, just changing the worksheet that the formula references. The workbook is to chart my progress in a variety of lifts, FWIW. For reference:

    For the estimated 1 RM column =OFFSET('Back Squat'!$E$2,1,0,SUMPRODUCT(--('Back Squat'!$E:$E<>""))-1,1)
    For the date the lifts were performed on =OFFSET('Back Squat'!$B$2,1,0,COUNTA('Back Squat'!$B:$B)-1,1)

    I have been able to then modify the formula for each chart to use the named ranges instead of the hard coded ranges, EXCEPT on two sheets, and I have no idea why. Everything else is exactly the same. Any idea as to why two of these worksheets would not want to update?