I thought I was adept at Excel, but this problem has me at wits end... I want to use a Dynamic Named Range in a chart so that I can have a "Rolling 60-day" chart that updates as new data is entered. I have done this in the past without problem. This particular dataset has a lot of blanks. I want to find the last non-blank cell (cell has value) and then get that plus the previous 59 data points (blank or not). I think I have way over-complicated this, but I am so far into the weeds that I cannot see my way out. My named range formula is: "=OFFSET(INDIRECT(ADDRESS(MATCH(MAX(AShift!$D:$D)+1,AShift!$D:$D),4)),0,0,-60,1)". This formula works fine when I am on the "AShift" worksheet. However, when I try to use the Named Range in a chart I am getting errors. I think it may have something to do with the "INDIRECT" function and charts. I am open to any solution (I would like to avoid VBA if possible - I am the only one at work who really knows it) I just need a dynamic graph. I think there is a simple solution, but it is beyond me at the moment. Any help is greatly appreciated. I am attaching a pared down version of the file that show the problem and a few of the many things i have tried...
Thanks!
Bookmarks