Attached is an excel file that is redacted. What I'm looking for. 1) Setup of an excel file that as pivot tables (multiple) that will auto update when new data (this file as example) is loaded into the worksheet 2) Pivot tables needed (with filters for plant and Shp plant) a) forecast product name by unit, with total volume, sorted by volume (largest to smallest) b) forecast product name with volume sorted by largest to smallest with calculation that ranks the product on volume c) intermediate product with total volume sorted by largest to smallest, with additional calculations on average monthly volume and standard deviation of the monthly volume, and a calculation on whether the volume is moving up, staying the same or moving down over time. d) additionally the intermediate products need to be ranked based on volume e) FYI the column % is the percentage of the intermediate that goes into product name. it normally will sum to 1 for each product, but there maybe examples that don't, have a flag that highlights in some way that the % doesn't sum to 1. f) A pivot table that has the product name, and the intermediates (with %), with total volume sorted largest to smallest. g) If there is a way to be able to designate a certain ranking range of a intermediate that it could auto designate a value for the product name. This is confusing to explain. For example, let's say that the top 10 (user adjustable) intermediate products have a value of 1, the intermediate products ranked 11-20 have a value of 10, and the intermediate products ranked 21 - x (end) have a value of 100. I want to be able to say that if a product name has a value of the sum of those intermediate prod numbers.
So there will be several pivot tables with the above data. I want to be able to copy and paste new data into a "raw data" worksheet and then have the pivot tables update based on the new data. Some minor manipulation of the cell ranges or refresh data, etc., is acceptable.
A short text document (with screenshots) that explains the steps to complete to refresh the data is needed also.
For this test, filter the products by those that column N (Shp Plt) is only Plant 2D and Plant 2X. Columns J and G should only include those that begin with Product or Intermediate.
I expect that there would be a couple reviews (edits).
