Creating a Dynamic Chart

your dynamic range is based on how many rows there are and count back number of rows based on F1
so essentially it gives you the last "F1" number of days

if you now fill formula down to A500, how does your dynmanic range determine which date it should count back from?

that was a question for you as it is your data
i dont know how your data is setup

is your data in column A actually a date or a text field that reads like a date?
on your sample file it acts like the later

the problem with your setup if that if you link the cell to another file
if it is blank it will zero making it "real data"
so to circumvent this you need a way to determine the different between zero being blank and zero being zero

if its date then you can do a count back (F1) based on date
use match formula to determine the "last row" then minus F1 to get your start

with that you can then rearrange your named range based off this

i cant get your sample file to act the way it should probably due to then "link"
IE
Thu Dec 24, 2015
looks like a date, cell is formatted as date but cell reacts like text