We recently changed to Excel (Office) 365.

I've been tasked with creating a template for a series of line charts that will be based on dynamic arrays. I watched some excellent tutorials online, but I'm having a problem getting the chart to accept the input of a dynamic array.

I started by converting my data into a table. I tried using an Index (Match): Index(Match) formula to select the rows between two mileposts (numbers) that can be chosen with two data validation dropdowns. Milepost will be my horizontal axis; there will be anywhere from 4 to 10 columns of data that will be plotted. 365 shows me spilled results that are correct. But when I attempt to make that array formula a named range in Name Manager, and then use that Name in my horizontal access, I get "there's an error in the formula you entered."

=INDEX(Milepost,MATCH(hma_Data!$P$2,Milepost,0)):I NDEX(Milepost,MATCH(hma_Data!$P$3,Milepost,0))

I see that it's replaced selected range (hma_Data!$A$2:$A$500) to the named range "Milepost."

I watched another video that seemed to based the chart on the actual spill array, by selecting the cell where the spill started, and using that cell location ($R$2 in my case) with the # sign. I made this a named range. The chart will not accept that named range either, nor the direct formula of R2#.

I feel like I'm missing the smallest most obvious thing to get it to 'stick' in the chart. Blindspot!

Thanks for any suggestions,

Ann