Sparklines -'Data source reference not valid'

bark01

Board Regular
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.

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
Try putting that formula into a defined name and then use the name in the source data dialog.

Replies
5
Views
142
Replies
2
Views
186
Replies
1
Views
353
Replies
14
Views
169
Replies
1
Views
273

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.

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

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