I have a pivot table that shows weekly sales by sales person and their achievement against a target. The report covers multiple weeks and there is a report filter that allows the user to select a specific week or multiple weeks. The filter list is based on week ending dates.
The issue I have is that the sales data is pulled from a database and as mentioned can return data for multiple weeks. The targets however are only set by sales person for a week. These targets are manually input in a separate worksheet.
What I need to do is multiply the target values by the number of weeks that the user selects in the report filter. So for example if sales person Joe Bloggs has a weekly target set at £4000 and the user selects two week ending dates in the report filter I want the report to show the two weeks actual sales and a target of £8000.
Is there a way to return a value for the number of items selected in the report filter that I can then use in a calculation to multiply the target values?
If I need to use VBA to get this then this is fine.
Bookmarks