Sparklines -'Data source reference not valid'

[bark01]

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.

 

[RoryA]

Try putting that formula into a defined name and then use the name in the source data dialog.