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

Kasmoosi

New Member
Joined
Nov 26, 2015
Messages
2
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,065
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Nov 26, 2015
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,351
Messages
5,595,650
Members
414,006
Latest member
Davefromlondon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top