Sparklines -'Data source reference not valid'

bark01

Board Regular
Joined
Sep 6, 2006
Messages
118
Hi,
I've never used sparklines before and have been asked to include them in a dashboard. That has a large number of KPIs so I'm trying to create dynamic sparkline that I can create against the first KPI and copy against all other KPIs

I've got a formula that will, upon error, show me its referencing the correct range but won't show the data. the error I get is 'Data source reference not valid'

The sparkline is on a different tab to the source data. the source data is on the tab 'input(total)'

I can't get the first one working, but my aim is to have a graph I can drag down to each row as each row is a different kpi.


This is what I'm trying to paste in the data range:

=OFFSET('Input (total)'!$B$2:$BF$256,VLOOKUP((F6,'Input (total)'!$B$2:$BF$256,2,0)-1,6,1,COUNTA('Input (A2)'!H235:BG235))


'Input (total)'!$B$2:$BF$256 - This is the range that covers all the KPI source data, each row 2 - 256 is a different KPI and each column is week.
The range is static for all KPIs

VLOOKUP((F6,'Input (total)'!$B$2:$BF$256,2,0)-1 - this look up the KPI number associated with the line the sparkline is on. I.e this sparkline is on row 6 the KPI number associated with this row is 5 and is stored in cell f6.
If I look up 5 in the input tab it tells me what row the associated data is, I was one out so added -1 to the end.
The aim of this is to be dynamic so each sparkline can have the same formula put pick up a different row in the source data

,6,1, this states where the first week of the year is (column 6) and that I only want one row of data
Static for all KPIs

COUNTA('Input (total)'!H235:BG235)) this is a dynamic count of the weeks year to date to show how many columns to include in the sparkline
Dynamic as teh number will change each week, but will return the same number for all KPis.




Thanks for any help.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try putting that formula into a defined name and then use the name in the source data dialog.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,061
Messages
5,628,369
Members
416,314
Latest member
Dan99321

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