Vertically Scrolling Dynamic Sparklines

[hennakao]

Hi there, I'm new to this forum and currently struggle with an Excel problem relating to a dashboard project I am working on Hopefully someone can enlighten me.

I have a window of vertically scrolling data and would like to add sparklines, showing a 12 month rolling trend next to it. The sparklines should scroll with their respective data.

How can I accomplish this with an INDEX formula? I am trying to avoid OFFSET because it is more resource intensive.

Please see attached my workbook. Any help would be greatly appreciated.

Thank you!

 

[John Jairo V]

Hi @hennakao

I create a name (Trend) with this formulae:

=INDEX(Workings!$P$4:$AA$26,MATCH(C4,Workings!$C$4:$C$26,),)

And refer this name into sparklines.

Please check file. Blessings!

 

[r2c2]

Hi @hennakao

I create a name (Trend) with this formulae:

=INDEX(Workings!$P$4:$AA$26,MATCH(C4,Workings!$C$4:$C$26,),)

And refer this name into sparklines.

Please check file. Blessings!

Awesome @John Jairo V

@hennakao While named ranges for sparklines work very well, they are cumbersome to deal with and offer some limitations (compared to how named ranges can be used in charts or formulas).

I suggest setting a 7 row range in your workings tab that extracts the relevant data based on scrollbar position. Then you can point the sparkline range to the 7 row range. This offers a lot of flexibility too (if you want to add more rows to the dashboard etc.)

 

[hennakao]

Hi @hennakao

I create a name (Trend) with this formulae:

=INDEX(Workings!$P$4:$AA$26,MATCH(C4,Workings!$C$4:$C$26,),)

And refer this name into sparklines.

Please check file. Blessings!

You've made my day. This is awesome and was exactly what I was looking for.
Learned something new.

 

[Hui]

R2C2, what are the limitations of Named Ranges you refer to?