+ Reply to Thread
Results 1 to 5 of 5

Stopping Selected Cells from Calculating Formula

Hybrid View

  1. 11-18-2013, 09:51 PM #1
    bouncingbudha
    bouncingbudha is offline
    Registered User
    Join Date
    09-03-2012
    Location
    Christchurch New Zealand
    MS-Off Ver
    Excel 2013
    Posts
    45

    Stopping Selected Cells from Calculating Formula

    Hi all

    I have 12 collums with over 2000 rows containing a Sumproduct formula counting 3 Variable’s in each cell.
    =SUMPRODUCT(--(June!$H:$H='Monthly and YTD Totals'!$A2224),--(June!$I:$I='Monthly and YTD Totals'!$A$37),--(June!$B:$B='Monthly and YTD Totals'!$O$132))
    This is causing the Workbook to opperate very slowly
    Can i turn off the automatic Calculation on these Cells?
    I have some other cells containing formula that i would like to keep running
    Does anyone have any ideas on how this could be done?

    Thanks
    Reply With QuoteRegister To Reply
  2. 11-18-2013, 11:37 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,950

    Re: Stopping Selected Cells from Calculating Formula

    Maybe define named ranges instead of looking at the entire column:
    June_B: =OFFSET(June!$B$1,0,0,COUNT(June!$B:$B))
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 11-19-2013, 01:23 AM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Stopping Selected Cells from Calculating Formula

    Another suggestion would be to not use entire column ranges in teh SUMPRODUCT(), that will slow it down. Rather, restrict the ranges to just what you need
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 11-20-2013, 12:23 AM #4
    bouncingbudha
    bouncingbudha is offline
    Registered User
    Join Date
    09-03-2012
    Location
    Christchurch New Zealand
    MS-Off Ver
    Excel 2013
    Posts
    45

    Re: Stopping Selected Cells from Calculating Formula

    Thank you both for your suggestions
    I have restricted the ranges in the sumproduct formula and this has solved the issue.
    Reply With QuoteRegister To Reply
  5. 11-20-2013, 01:06 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Stopping Selected Cells from Calculating Formula

    Happy to help and thanks for the feedback
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need help with formula for calculating fees based on product selected from list
    By Tenyrsgone27 in forum Excel General
    Replies: 7
    Last Post: 04-22-2012, 02:54 PM
  2. Help on stopping cells from changing
    By debtless in forum Excel General
    Replies: 9
    Last Post: 05-16-2011, 09:22 PM
  3. Formula on selected cells
    By Frank e in forum Excel General
    Replies: 5
    Last Post: 10-13-2008, 12:18 PM
  4. Manual calculating selected cells only
    By pauldaddyadams in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-11-2008, 04:05 AM
  5. [SOLVED] Calculating Selected Cells
    By NlCO in forum Excel General
    Replies: 2
    Last Post: 08-10-2005, 07:39 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1