Spark line indirect reference error: "Data source reference is not valid"

[Kasmoosi]

Hi,

It's my first post here. Searched help all over the web but didn't find anything helpful on this, so here we go:

I want to create a report with rolling 12 month sparklines. The report is updated monthly and I find it convenient to calculate the range in one cell and then refer to it using =indirect() function.

I have monthly data and tried to create spark lines using =indirect() formula as a reference. When I put the function in Data Range -field and press "OK", the error message pops up. However, after pressing "OK" on the error message the sparkline selects correct data A2:F2 but it seems, that for some reason, I just cannot add sparklines using the =indirect().

In my example I have quite heavily simplified the problem:
https://www.dropbox.com/s/vcl3b0hwj22xq6o/sparkline.PNG?dl=0

I would appreciate any ideas how to make sparklines work using =indirect() or some other ideas how to easily move the range to which the sparkline refers to.

 

[RoryA]

Welcome to the forum.

Create a defined name that uses the INDIRECT formula you want, then use that name in the Sparkline dialog instead of the formula.

 

[Kasmoosi]

Thank you Rory! Now I know how to use =INDIRECT() in sparkline.

Weird that the formula in question cannot be used directly in sparkline though.

I created a Defined Name having a dynamic range and can easily create the sparklines for all the lines I need.