Sparkline Dynamic Range

leeshan

New Member
Joined
Jan 27, 2013
Messages
24
i want to do a sparkline for a dynamic range of data. means whenever i add the data, the sparkline will grow automatically. i tried using the offset formula as shown,
=OFFSET(H9,0,0,1,COUNTA(H9:Q9))

But, when i paste this into the sparkline data source, i keep getting the "Data source refrence is not valid".
Any idea where went wrong?

Thanks!
 
Insert a name that refers to:

=OFFSET(Sheet1!$H$9,0,0,1,COUNTA(Sheet1!$H$9:$Q$9))

and use that Name as the SparkLine's Data Range.
 
Upvote 0
On the Ribbon's Formulas tab click Name Manager. On the Name Manager dialog click New. In the Name box on the New Name dialog type Test; in the Refers to box type:

=OFFSET(Sheet1!$H$9,0,0,1,COUNTA(Sheet1!$H$9:$Q$9))

changing the worksheet name if necessary. Click OK then Close.

Select the cell where you want the Sparkline. On the Ribbon's Insert tab click the Sparkline you want to use. On the Create Sparklines dialog type Test in the Data Range box and click OK.
 
Upvote 0
I have one more question...

for eg, VLOOKUp and HLOOKUP helps us to look up for value. now i want to match the sparkline, as in lookup the sparkline for a particular region for example then display it in another tab. What formula should i use?
Thanks a lot!!!!
 
Upvote 0
You can't lookup a sparkline, but you can lookup its source data. If you post some sample data and the expected results I will try to help you.
 
Upvote 0
for eg, this tab is my raw data,

JAN FEB
12, 13,2, 34 (sparkline here) 10,6,14,1 (sparkline here)


then, i would like to lookup the sparkline in another tab,

(here will be a drop down list for month)

Trend (Sparkline shown here from the raw data tab)



Basically i want to do something like VLOOKUP, whenever i select the month in th drop down list, i would get my correspoding sparkline in the raw data.
Thanks a lot!!!
 
Upvote 0
Yaya. My range is dynamic. From column A to E for example. If i update the data at column E, my sparkline will get updated too. As well as the one that i look up in another tab.
Thanks!!
 
Upvote 0

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
Back
Top