Dynamic Named Range formula with Indirect and Index for Chart

[wmfinn001]

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!

[protonLeah]

daterange

Please Login or Register  to view this content.

LIne1_A

Please Login or Register  to view this content.

[Pete_UK]

I used this array formula in the Refers To box for the named range Line1_A:

=INDEX(AShift!$D:$D,MAX(IF(AShift!$D$5:$D$189<>"",ROW(AShift!$D$5:$D$189)))-60):INDEX(AShift!$D:$D,MAX(IF(AShift!$D$5:$D$189<>"",ROW(AShift!$D$5:$D$189))))

This uses INDEX twice to define the start_cell and end_cell of the range, and I suppose you could use MAXIFS instead of MAX(IF( ...

Anyway, you can then use this formula in cell L5 of the Chart sheet:

=INDEX(LIne1_A,ROWS($1:1))

and copy it down 60 rows to check on the values that it encompasses.

There has always been a problem when two volatile functions (OFFSET and INDIRECT) are used in conjunction with one another.

Hope this helps.

Pete

[wmfinn001]

SOLVED! Thank you both for the fast reply! Pete_UK, thanks for the education on using two volatile functions together - it worked fine in the formula, but I guess Charts don't play nicely with that. I am going to use protonLeah's suggestion so I don't have to repeat the data on the Chart sheet. pL, an you explain how you used the 9E+303 in the Match function? How did that get the correct row?

[protonLeah]

When using the MATCH function, match type 1 (or omitted) is the default; thus, MATCH finds the largest value that is less than or equal to lookup_value. 9e303 is just a very big number