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!

 

[btsiliacos]

Sorry, I'm not sure what you mean. But I do see that my name definition was wrong. It should be:

=OFFSET(Sheet1!$A$2,0,MATCH(Sheet2!$A$1,Sheet1!$1:$1,FALSE)-1,1,4)

Hi, my problem is very similar but my data is formatted as so and I would like the partner IDs on sheet 2 to match up with sheet 1 to create a dynamic sparkline that grows as new data is inserted. I've been working on this for hours with no success. Your help will be greatly appreciated.

[Sheet1]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Partner[/TD]
[TD]8/1/2016[/TD]
[TD]8/2/2016[/TD]
[TD]8/3/2016[/TD]
[TD]8/4/2016[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB-1497416[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1232[/TD]
[TD]23928[/TD]
[TD]23212[/TD]
[TD]21323[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB-1497416[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]123423[/TD]
[TD]4243636[/TD]
[TD]4523423[/TD]
[TD]234234[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB-5208278[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]642342[/TD]
[TD]234234[/TD]
[TD]442345[/TD]
[TD]4234234[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB-6297468[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]524234[/TD]
[TD]324234[/TD]
[TD]423423[/TD]
[TD]4323432[/TD]
[/TR]
</tbody>[/TABLE]

[Sheet 2]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Partner[/TD]
[TD]Sparkline[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB-1497416[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB-1497416[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB-5208278[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB-6297468[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Latest post re-posted here: http://www.mrexcel.com/forum/excel-questions/961876-dynamic-sparkline-w-offset-match.html
I also deleted your other duplicate posts.

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

 

[btsiliacos]

My mistake. Thanks for keeping me in check! Any help with the issue above would be appreciated.

 

[cmanoj78]

i created a named range using offset function and provided that as a data range for column sparkline. However while the sparkline is created properly, it doesnt pickup any update on the dynamic range. if i create a new sparkline it gets created with the updated number of columns but then again doesn;t update upon changes to the range. Any solutions ?