A static-dynamic bar chart - is it possible?

chart.jpg

Hello,

while working on a report automation i ran up against a problem that have so far seemed unsolveable.

I have a bar chart which in all other conditions would have been possible to be made dynamic with the usual OFFSET + COUNT/COUNTA (it is updated weekly and should display data for the 9 last weeks only), but there's one parameter that must maintain a fixed reference. It's marked with red on the diagram. I would like to keep the other 9 bars dynamic (by an OFFSET-COUTNA-based named range) and still be able to add this one static number.

oś.jpg

Nonetheless, every time I combine the two, the dynamicity of the name-based range is lost and the chart automatically sets this range to static.

Is there any know solution to this problem? I would be grateful for any hints.