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.