Automated Dashboard

[Dlinen]

Hi Guys,

I am trying to make myself a dashboard that is updating itself as much as possible. I have one data sheet with all the source data which is in the following format:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Startdatum[/TD]
[TD][/TD]
[TD][/TD]
[TD]new members[/TD]
[TD]New member %[/TD]
[TD]page views[/TD]
[TD]visits[/TD]
[/TR]
[TR]
[TD]4.1.16[/TD]
[TD]wk[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11.1.16[/TD]
[TD]wk[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18.1.16[/TD]
[TD]wk[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]25.1.16[/TD]
[TD]wk[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1.2.16[/TD]
[TD]wk[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]1%[/TD]
[TD]4086[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD]8.2.16[/TD]
[TD]wk[/TD]
[TD]6[/TD]
[TD]21[/TD]
[TD]8%[/TD]
[TD]1547[/TD]
[TD]256[/TD]
[/TR]
[TR]
[TD]15.2.16[/TD]
[TD]wk[/TD]
[TD]7[/TD]
[TD]32[/TD]
[TD]11%[/TD]
[TD]1685[/TD]
[TD]291[/TD]
[/TR]
[TR]
[TD]22.2.16[/TD]
[TD]wk[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]3%[/TD]
[TD]1032[/TD]
[TD]258[/TD]
[/TR]
[TR]
[TD]29.2.16[/TD]
[TD]wk[/TD]
[TD]9[/TD]
[TD]41[/TD]
[TD]10%[/TD]
[TD]2909[/TD]
[TD]403[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]19.12.16[/TD]
[TD]wk[/TD]
[TD]51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26.12.16[/TD]
[TD]wk[/TD]
[TD]52[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum[/TD]
[TD]=visits/new members[/TD]
[TD]Sum[/TD]
[TD]Sum[/TD]
[/TR]
</tbody>[/TABLE]


and then I have a dashboard which summarizes all the data, which I would like to have updated automatically so i only have to fill in the new data in the data sheet.

Dashboard:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]New Members[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]41 (1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sparkline last 12 wks (4)[/TD]
[TD]% change between wk 9&8 (2)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]"vs Wk 8" (text that shows vs WK & the second last week with data (3)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In the dashboard the following things should be calculated:

1. New members: Show Value of Last Cell in column Members
2. Growh rate vs. previous week: ((Value of last cell in Members)-(Value of second last cell))/(Value of second last cell)
3. Description cell: Text "VS Week (second last week, i.e. if the last data was entered in week ="vs wk " & 'Sourcedata total'!$C$39
4. Sparkline with the last 12 weeks of data


I hope i explained it in a way that makes sense. feel free to reach out and ask questions. I do have a sample xlsx doc to share if that would help.

Thanks, Dlinen