Dynamic Chart Axis

[EmmaFairclough]

Please help!
I have 36 revenue graphs to update on a weekly basis, in order to simplify this I have linked the data sheets to the source document. However I only want to view a rolling 52 weeks within my graph. I have managed to create dynamic named ranges to populate my graph as new information is added. (still not able to drop off the old weeks - but it's not my most pressing problem for now)
However...
My first problem was Excel sees the formulas as data and was plotting zero's. I overcame this by wrapping an IF around the formula to return #N/A if the data from the source document was 0. Problem solved - almost!
The Axis of my graph is still returning the full range, when there is no data at all in the range the axis adjust (as they should) but I obviously don't want to have to re-link each document on a weekly basis.
My ideal scenario would be to just be able to open the document and print out my nicely updated graphs.

I have been searching the internet for two days to no avail!
Any help is appreciated and accepted gratefully!

 

[Andrew Poulsom]

What do your dynamic ranges refer to?

 

[RoryA]

Cross-posted here with more detail and sample sheet.