INDEX based dynamic range creating errors with LINEST

I've created a spreadsheet which uses named dynamic ranges created with an INDEX formula since they are non-volatile. For example =Sheet1!$D$5:INDEX(Sheet1!$D:$D,Sheet1!$B$8 + ROW(Sheet1!$D$5)-1)
These dynamic ranges form the x and y value ranges in some LINEST formulas.

Everything has worked well until I used a macro to modify some of the values contained in the dynamic ranges which are the inputs to the LINEST formula.
For example say I have cell B3 with a value of 2 which is added to each of the cells in one of my dynamic ranges.
If I type 3 in cell B3 and press enter everything works fine.
However if I use a simple line of code in a macro such Range("B3").Value=3 then I get a #VALUE! error from the LINEST formula. All other formulas referring to the dynamic range behave normally.
If I use a LINEST formula with hard coded input ranges or OFFSET based dynamic ranges then it works fine.

I have attached a workbook which shows a simplified example of this issue.
If you change the purple cell manually it works fine but if you use the button to change it then it causes an error.

Can you tell me what's going on?

Thanks

Isn't the problem with your named ranges...

Shouldn't

=Sheet1!$D$5:INDEX(Sheet1!$D:$D,Sheet1!$B$8 + ROW(Sheet1!$D$5)-1)

be

=Sheet1!$D$5:INDEX(Sheet1!$D:$D,Sheet1!$B$8 + ROW(Sheet1!$B$5)-1)