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]

Is there always a blank cell between each month's data, like E2 and J2 in the example I posted?

 

[leeshan]

there is blank cell if and only if the data of the month is not available yet. For example, now is march i will only have data until march, frm april to dec will be blank.
Thanks!!

 

[Andrew Poulsom]

Are there always 4 entries for each month?

 

[leeshan]

yes

 

[Andrew Poulsom]

You can use a name that refers to:

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

 

[leeshan]

in sheet2 ( the tab that i want to lookup the sparkline), i click on the cell and insert sparkline again then use the one as the data source or?

 

[Andrew Poulsom]

Yes, that's right.

 

[leeshan]

when i define the name to the formula, everything still fine. But when i insert the sparkline and use the name as the source, i check where the source it refers to, it has changed!! Any idea why this happen? The name refers to the correct source but the problems come after i insert the sparkline.
Thanks a lot.

 

[Andrew Poulsom]

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)

 

[kgasaway]

This was the most helpful piece of advice I've gotten. Thank you!