+ Reply to Thread
Results 1 to 6 of 6

Processing issues on Planning Sheet

  1. #1
    Registered User
    Join Date
    04-15-2020
    Location
    Utrecht, Netherlands
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9

    Question Processing issues on Planning Sheet

    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!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Processing issues on Planning Sheet

    Firstly, you are using full column references, such as $N:$N - this is not good practice. Limit the ranges to use just the number of rows necessary. Try this first and see if it makes a difference.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Processing issues on Planning Sheet

    I changed something:

    From I3 than drag accross:
    Please Login or Register  to view this content.
    I4=D4
    then drag accross

    Formula in D6:

    Please Login or Register  to view this content.
    Drag down and accross
    Attached Files Attached Files
    Quang PT

  4. #4
    Registered User
    Join Date
    04-15-2020
    Location
    Utrecht, Netherlands
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9

    Re: Processing issues on Planning Sheet

    I can't believe I missed out on such a simple thing. Thank you! Works way quicker now!

  5. #5
    Registered User
    Join Date
    04-15-2020
    Location
    Utrecht, Netherlands
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9

    Re: Processing issues on Planning Sheet

    Quote Originally Posted by bebo021999 View Post
    I changed something:

    From I3 than drag accross:
    Please Login or Register  to view this content.
    I4=D4
    then drag accross

    Formula in D6:

    Please Login or Register  to view this content.
    Drag down and accross
    Thank you so much! This worked absolutely great. You are the man!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Processing issues on Planning Sheet

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Setting up a production planning sheet with Macros
    By cbhuta in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2020, 10:26 AM
  2. [SOLVED] Help with material requirement Planning (MRP) formula in my sheet
    By samdora in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2019, 09:27 AM
  3. Replies: 1
    Last Post: 06-01-2014, 09:02 PM
  4. Sheet-related processing causes Excel VBA Userform to stop processing Tab/Enter
    By Joaquin M Lopez Muno in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-22-2014, 03:02 PM
  5. Reduce processing time for processing multiple excel sheets
    By rexer231 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2014, 09:25 AM
  6. Looking for Interval wise manpower planning sheet.
    By Ajaykalyan in forum Excel General
    Replies: 1
    Last Post: 12-30-2012, 12:46 PM
  7. Set up Tax planning sheet by week/monrth- Income/deductions
    By Burt1921 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-04-2006, 05:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1