Basically this is the only way i've managed to get it to work.
I've attached a simplified version focusing only on the Sales Volume, if this part can incorporate the sales curve by month then i can apply it elsewhere.
Overview: In the attached file
Tab 1 - Pivot table output
Tab 2 - Database table data (JA wouldn't allow me to upload the SQL database so i have include the data here)
1. 'COA' - holds account/product info
2. 'Date' - date plus associated date driven data for calcs eg. Sales Curve
3. 'Plann' - holds assumption data used in calculations.
Tab 3 - Trigger query
This is associated with the 'Plann' table - The purpose of the query is to auto update table 'COA' column 'PL' driven by assumptions in table 'Plann'. In my version of excel i have an addin that allows sql table data to be changed in an excel worksheet.
If you have a more efficient way of coding it would be very much appreciated.
If anything isn't clear please let me know.