Dynamic Year-To-Date Sparkline based on week chosen from Dashboard.

[theexcelcrowd]

Hello,
I'm quite stuck with this issue and lost on what should go into the data range for the sparkline while using the Offset function as my reference value is in another workshet and combining vlookups in Offset is leaving me confused.
In my dashboard worksheet, I have a list [validation] made up of weekly dates [week ending on]. After the user selects a particular week from the drop-down, I want to show a sparkline chart that shows the Year-To-Date trend in clicks till the chosen date.
The weekly data is within a range in another worksheet where I have the summary date. This would look something like:
Column A Column B
Week Ending Clicks
7-Jan-2016 50
14-Jan-2016 100
21-Jan-2016 50
If my chosen period is for week ending 7Jan [from dashboard], the sparkline should be a dot.
If I choose 14th Jan, the sparkline should have two data points. If I choose 21th Jan, the sparkline should have three data points.
Thanks!

 

[pbornemeier]

Use this formula against your dashboard validation selection (F4) and validation list (A:A) to return the index of the selected value:
Location: Sheet2 H4
Formula: =MATCH(F4,A:A,0)

Create a named range in your dashboard worksheet that pulls data from your WeeklyData worksheet,:
Name:
Formula: =OFFSET([WeeklyData.xlsx]Sheet1!$B$1,0,0,Sheet2!$H$4,1)

Use
Formula: =Weekly
as the Data Range for your sparkline

 

[theexcelcrowd]

<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 1.42857em; padding: 0px !important; background-color: transparent;">Hi,
I asked this in www.reddit.com/r/excel and got a suggestion to use match at the end as the lookup value is in another sheet.
=OFFSET($B$2,0,0, MATCH($D$2, $A$2:$A$50, 0), 1)
</code>