Currently I am building an automatic planning sheet within Excel. This sheet is divided into two separated sheets, where one is a global view of the allocated project on every time stamp for each worker on each day, and the other is where I pull the data from. Each day is separated into four quarters, which is the bare minimum to plan with.
Explanation of the Excel file:
In the sheet called ‘Projectenlijst’ is where all raw data is put in. It’s this sheet where to select the worker, fill in the project number, starting date and starting quarter of the project and the end date plus ending quarter of the project. See the attached example to understand. In the sheet ‘Overzichtsplanning’ is where I want this raw data to be put in. For example, if I put data on the ‘Projectenlijst’ with an assigned worker, a projectnumer with a start- and enddate including the startquarter and endquarter, I want to visualize this in the sheet ‘Overzichtsplanning’.
The issue:
Currently I am using a formula that includes a lot of aggregate functions which is just hard-pulling my CPU every time I change a cell. Using the manual calculation doesn’t work because Excel will just crash on calculation due to all the cells containing this formula. I got the UI pretty much set up, the only thing is these formula’s to have less processing issues on the ‘Overzichtsplanning’. I have added an example as attachment.
The question:
Is there a better and easier way to get the lookup of the given data, without having serious processing issues? I am so lost in the different formula's to use, that I cannot even recall what formula's I already have tried. Basically what I want the formula for each cell to do is to find any project for this worker in range of the specific day including the range of the quarter of the day if the specific day is equal to the startdate of enddate. All this, but then with way less processing power then the aggregate formula I use right now.
If you need more information, please let me know! Thank you very much in advance!
Bookmarks