HI,
My working SCENARIO:
I AM PREPARING A PRODUCTION SCHEDULE PLAN (MICRO LEVEL). IN OUR FACTORY WE HV THREE TYPES OF MACHINES I.E TYPE-A , TYPE-B and TYPE-C.
In TYPE-A machine we have 10 numbers
In TYPE-B machine we have 8 numbers.
In TYPE-C machine we hv 12 numbers.
total we hv 30 machines in our factory. when any order is recd, we start allocating the order to particular type of machine depend upon the reqmt. the production start date will be decided based on raw material arrival date and end date is fixed based on delivery date of that order. Number of machines allotted to particular order is depend upon total ord qty of that order and number of days available for production.
normally this planning is done for next 3 months. attached excel sheet, where the work sheet with name "master", shows the number of machines available for each date till march end.
all details are given in the excel sheet.
WHAT I WANT:
from the master sheet, pivot table is generated in next sheets with name "PIVOT-1" and "PIVOT-2". The SUMMARY Sheet data is linked to PIVOT-2 and the formulas used in this are absolute ranges refering the pivotal table. I need this formulas to be dynamic and when PIVOT-2 is expand or shrink, the range should automatically or dynamically modify itself to give proper result.
is there any other concept by which i can simplify this process of allocation instead of pivot table or entering master sheet.
Bookmarks