Creation of Dynamic Sparklines

Hi dearest Excel Wizards ,

I am trying to create a report where I would like to have dynamic Sparklines and dynamic data in it.

Essentially I would like to automate as much as possible so that when I enter new data and refresh the formulas (in case the file is too big with auto refresh) the report will essentially generate itself.

For the sparklines I would like to have the last 12 weeks and for the data the last weeks. I have tried working with the offset formula but for some reason it constantly breaks

Attached a sample set of my data & report.

Can anyone help me how to set up the offset formula so that it works? Thanks a lot in advance!!!

Originally Posted by Andy Pope

create a named range, and use that as your data source.

Not sure why there is empty rows at the top of your data so this goes from the start of the values.

=OFFSET('Sourcedata total'!$D$15,COUNT('Sourcedata total'!$D$15:$D$54)-12,0,12,1)

Thanks andy! i had the empty rows at the top for titles. But changed that so it works now. I have two more questions for you though:

- how do you automatically just pick the latest number in a column?
- And secondly, how do can you do the weeks i.e. i'd like the Dashboard to take automatically the second last week with data so i can have the 'vs week XY' automated as well... any tips theere too?

Cheers,
Dlinen

I created a name for each sparkline with a formula like this for example: =OFFSET('Sourcedata Total'!$W$2;COUNTA('Sourcedata Total'!$W2:$W53)-12;0;12)
Now i have the issue that it randomly changes this formula when i continue to add data... i.e. to this: =OFFSET('Sourcedata Total'!$W$2;COUNTA('Sourcedata Total'!$W1048558:$W33)-12;0;12)

there is no logic behind in my opinion... any idea what that is?

Thanks!!! got it!

Andy, you are a lifesaver by the way... i was about to go mad