Excel 2010 dynamic filename for named range in chart series

Hello!
This is my first post so hello everyone.

At first, what I want to achieve:
I need to create named ranges for charts to keep last X rows only, there is no problem with that and it works, but...

Problem:
To use named range as a chart series I need to specify filename followed by the range name, e.g. 'Book1'!range1, the problem is that this is a weekly report and the name contains week number - it changes every week, it also contains version number which can change multiple times a day. The report contains many charts * many series so it is really hard to manually change the file name for each series.

Question:
How to use dynamic filename to specify named range in chart series? It would be best if this could be done without using VBA - it has to be macro-free file.

Hello Kaper,
I don't really understand your solution. Could you please explain it further?

PS. zawsze lepiej dodać załącznik z przykładowymi danymi - see http://www.excelforum.com/forum-rule...rum-rules.html under numbered part.

Regarding this annotation, I think that there is no need to attach any data to the thread as the problem is not related to data itself.