Dynamic target line in Pivot Chart?

Dear Excel Fans,

I would really need some advice on how to create dynamic target line in Pivot Graphs. I did a lot of research, also found here a few topics where it’s mentioned, but I couldn’t find anything similar to the report I need to do.

Therefore I spent some time (like a lot :D) to put together an example that represents what I will need. (Apologies for the very detailed example. It might look weird and maybe even some parts not clear why created they way they are, but it very closely mirrors the actual report I’m working on).

Here we go:

The given company has 6 Sales Agents, selling a certain product to Small, Medium and Large businesses in two locations in the United States, New York and Los Angeles.

• Agent1: belongs to SBD department that deals with Small businesses in New York
• Agent2: belongs to SBD department that deals with Small businesses in Los Angeles.
• Agent3: belongs to MBD department that deals with Medium businesses in New York
• Agent4: belongs to MBD department that deals with Medium businesses in Los Angeles
• Agent5: belongs to LBD department that deals with Large businesses in both New York and Los Angeles
• Agent6: belongs to LBD department that deals with Large businesses in both New York and Los Angeles

Each Sales Agent, Department and Location has weekly / monthly / yearly targets:

• Nr. Of Closed Deals: represent the target of closed deals out of all the meetings the Sales Agent has
• Revenue from Closed Deals: represent the target revenue of closed deals out of all the meetings the Sales Agent has

You’ll find the below sheets in the sample excel:

• DATA: that shows all the data pulled from the CRM system
• MASTER DATA: pulled from the DATA sheet with additional columns that helped me creating the pivot tables and charts
• Targets: that shows the targets for each Sales Agent, Department and Location by weekly / monthly / yearly
• PIVOT_Meetings: created to show the results (TTL. Nr. Of Meetings and the number of Closed, Pending and Not Closed deals). I added slicers so I can choose to see the numbers only for certain Location, Department or Sales Agent. There is a small +/- button on the bottom right corner of the pivot chart, where I can also choose to see numbers by years, months or weeks.
• PIVOT_Revenue: created to show the revenue generated (Rev_All, Rev_NY and Rev_LA). I added slicers so I can choose to see the revenue generated only for certain Location, Department or Sales Agent. There is a small +/- button on the bottom right corner of the pivot chart, where I can also choose to see numbers by years, months or weeks.

And now what I like to add:
A DYNAMIC TARGET LINE

In more details:
Please see the attached PDF for examples I created for the PIVOT_Meetings sheet (the requirement s the same for the PIVOT_Revenue sheet).
Basically, what I like to have, is a dynamic target line that is connected to the data on the Target sheet, and moves depending on what I select on the slicer of the pivot chart.
The targets might need to be changed from time-to-time, so would need a solution that changes automatically when the targets change.
If there is even a solution...

I'm unable to upload the sample excel as it's MASSIVE (...). But I uploaded it on SendSpace and available to download from here (if that is even allowed... I'm not spreading any viruses, my file is just too big to upload here):
https://www.sendspace.com/file/o2rgfh

Anyone out there who created anything like this before? In a dream scenario it would be great to have a simple solution, without any VB, but I know that would be asking for too much

Thank you for your feedback in advance!