I have some code that creates a pivot table based on a worksheet.
One of the columns is a person's selling rate, another has their name, another has hours worked. The pivot table end up grouping them by name, with their selling rate multiplied by their hours.
Problem is, sometimes someone's selling rate has changed during the year. My problem is then that the pivot table still sees this as one person, and either sums, averages or takes a max of their selling rate.
What I would like to do is perhaps append " (old rate)" to their name if there's a change in selling rate so that when the pivot table is created it sees this as two people and doesn't average/sum/max their selling rate.
As an example, say my source sheet has:
Smith, John | 10 | £10.00
Smith, John | 20 | £10.00
The pivot table would correctly group this to:
Smith, John | 30 | £300.00
However if John Smith's selling rate changed to £15 during the year, my source sheet might say:
Smith, John | 10 | £15.00
Smith, John | 20 | £10.00
In which case, currently, my pivot table (incorrectly) end s up with:
Smith, John | 30 | £350.00
What I'd like to see is:
Smith, John | 10 | £150
Smith, John (old rate) | 20 | £200
Thanks!
Also posted here: