Cell Referencing Code Error?
 

I am trying to use naming functions to populate numerous charts with scroll
bars, and I would like the scroll bar to populate a range and when you scroll
right the whole range moves, ie 1-50, then 2-51, 3-52..., however in my
formula it doesnt seem to like the B part, it claims it is an invalid name
reference... please see the formula below and see if you can tell me how to
make it work. I have tried numerous concatenations, quotes, brackets etc but
the formula wont work....thank you all!!!!

=OFFSET('5 Yr Average Yield'!B(6+VALUE('5 Yr Average Yield'!$A$1)),0,0,52,1)