4

I cant seem to find even similar posts online on the following obstacle. I will have a lot of sparkline charts for all metrics I am using. See below:

enter image description here

I would like to pull the sparkline in the sheet with the dynamic data for the dashboard. I seem to be able to pull the data with following formula:

=INDEX(Data!$D$2:$FH$9,8,MATCH(StaggedData!$D9,Data!$D$2:$FH$2,0)+$F$6)

($F$6 at the end of the formula works as column offset)

Problem is when I try to do it with the sparkline I just get 0. Is there a way to pull sparkline chart into my dynamic data sheet and then I will pull the same sparkline into dashboard main sheet with a direct reference to dynamic data sheet.

drLecter
  • 197
  • 3
  • 18
  • the 2nd part of [this video](https://www.youtube.com/watch?v=MzF_5DXhqbs) will help you I think. it will take some setup work, but it's pretty nifty :) – Scott Holtzman Apr 07 '16 at 18:18
  • Hi Scott. I know about this trick and used it before. However it doesnt help me when its a sparkline and when it has variable position I have to pull with Index/Match and Hlookup :/ – drLecter Apr 07 '16 at 20:15

1 Answers1

1

If

  • Your sparkline is generated based on row number 4 and 5.
  • Your reference cell is D9
  • You are looking up D9 value in Row number 2

Then go to name manager and assign this formula to any name i.e. "RefData"

=OFFSET(Data!$C$2,1,MATCH(StaggedData!D$9,Data!$D$2:$F$2,0),2,1)

Go to the cell where you want to display sparkline. Insert sparkline and use "RefData" as Data Range.

Done!!

You can change the value in D9 and it will select the range dynamically and will display sparkline based on the values in row 3 and 4 for that particular column.

Vipul Karkar
  • 327
  • 1
  • 3
  • 11