Return a value for items selected in a pivot filter

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.

Hi Wayne,

Attached is an example of the file.

The information in the "Targets" worksheet is input manually. The information in the "Company Gross Margin Report" one is copied in from a csv export from a back office system. It is then converted/formatted etc via VBA.

If you look at the "Summary" tab you will see the pivot that summarises the data by Consultant Name. The "Target £" field is outside of the pivot and currently looks up for the target value in the "Targets" sheet. In the "Targets" sheet column D shows the £ Target Cumulative. This is calculated as the £ Target in column C multiplied by the value in cell B2 on the "Summary" sheet. Currently the user has to manually input this value based on how many values they have selected in the pivot filter field in cell B3. What I want is to get rid of the need for them to manually input this value but somehow pick it up based on the number of values selected in B3.

Hi

Each consultant will have multiple rows of data in the "Gross Company Margin report" sheet. These rows are consolidated in the pivot to give a total value for the number of weeks selected. The consolidated value is the one that the target value compares to. In the example file you attached you can see that for the first consultant, Joe Bloggs, you have selected one week of data. This one week actually consists of 73 lines in the source data sheet and therefore your target is calculating as 73 x £3000 = £219,000. The target value for this consultant for a 1 week period is only £3000.

The source data in the report is dropped in en masse each week with all previous data overwritten.

The 18K is the sum of the pay field for 73 lines in the "Company Gross Margin report" for the w/e 07/07/13. This represents the consultants total sales for that week. The consultants target is actually a Gross Margin target so in the pivot the sum of the Gross Margin £ for that week for those 73 lines is £3.9K. His total weekly Gross Margin Target is £3K so the report should show Gross Margin £ = £3.9K, Target = £3.0K, Variance to Target = £0.9K. If he repeated exactly the same sales for 2 weeks I would expect the pivot to show Gross Margin = £7.8K, Target = £6.0K, Variance to Target = £1.8k.

Hi,

Joe Bloggs is a recruitment consultant. The detailed sheets show time sheet details of all of the people who have worked for his clients. Column F shows the client which is a company. Column K shows the individual time sheet number. Columns N to P show the names of the people who have done the work. The hours relate to the workers shown on the time sheets. Joe Bloggs is targeted on the Margin earned by all of the workers allocated to him. The pivot summarises the Margin for all of these.

Hi,

Your solution works well but the data is imported from a csv that does not contain any formulas. I could add the formula using VBA but I was hoping there was a simpler solution where I could find a value of the number of w/e dates selected in the filter list.

Thanks for your help. I will add the formula if I can't find another solution.