Macro & Pivot Table Help Needed (and calculated fields)

[gkisystems]

On Mondays, Wednesdays, and Fridays I copy and paste data from our system into Excel. It represents a sales snapshot for the month. On Wednesdays and Fridays, it shows me the 2 day sales change whereas on Monday is represents a 1 day sales change (weekends are not sales days). I copy and paste the data from each day and then just add the new data at the bottom in a tab called "ALL CUST STATUS." On another tab called "Customer Change Pivot," I have a pivot table that pulls in today's date and the prior period's date totals beside each other in colums C and D. In column F, I have a formula that is D-C to give me the change between those two sales totals.


I have three problems I'm trying to solve with macros:


1. I cannot sort the pivot table based on my variances in column F and I want to. I believe this can be achieved by adding a calculated field into the pivot table instead of having a formula in column F.


2. If I do a calculated field, then that calculated field needs to change with each update to find the variance between the two most recent periods...how would I do that? Also, how would I update the pivot table filter to pull in the most recent 2 periods only?


3. How can I have a second calculated field that gives me the average 1 day change? It would have to be dynamic to know the number of days between this period and last period and then divide by the number of days in between those two periods.