Sparkline Dynamic Range

[leeshan]

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!

 

[Andrew Poulsom]

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.

 

[leeshan]

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.

I tried. But it gives me the same error..

 

[Andrew Poulsom]

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.

 

[leeshan]

It works now!! Thanks so much!!!

 

[leeshan]

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!!!!

 

[Andrew Poulsom]

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.

 

[leeshan]

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!!!

 

[Andrew Poulsom]

Is your data like this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	JAN					FEB					MAR
2	12	13	2	34		10	6	14	1
Sheet1

What range does it occupy?

 

[leeshan]

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!!