+ Reply to Thread
Results 1 to 9 of 9

trying to get a formula to quit calculating after another cell has changed

  1. #1
    Registered User
    Join Date
    04-16-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    19

    trying to get a formula to quit calculating after another cell has changed

    I am trying to get a formula to quit calculating after another cell has been changed.

    =IF($AL$2>=BF21,AH19,"")

    =IF($AL$2>=BF22,AH19,"")

    $AL$2 is the cell I change that number is equal to the BF21 or BF22 (the BF column is where my list is for a number dropdown).
    When I change the cell to equal the next number in the list. I want the previous formula(=IF($AL$2>=BF21,AH19,"")to quit calculating and give me the number(AH19) it calculated before I changed the number ($AL$2) from 1 to 2
    AH19 is a formula that can change by adding a number to its' base.

    This is for a role playing game character sheet I am developing.

    Thanks in advance

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,001

    Re: trying to get a formula to quit calculating after another cell has changed

    not sure i fully understand - can we see a sample spreadsheet - with the outcome required also shown
    but if you want to test two possible values will an OR work

    =IF(OR( $AL$2>=BF21, $AL$2>=BF22), AH19,"")

  3. #3
    Registered User
    Join Date
    04-16-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: trying to get a formula to quit calculating after another cell has changed

    here is my worksheet, be careful there is a lot going on.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,225

    Re: trying to get a formula to quit calculating after another cell has changed

    Hi and welcome to the forum

    No kidding you have a lot going on there

    I know this isnt what you asked, but a quick suggestion for something else...

    in BC21 downwards, instead of this...
    =SUM(AM21:BB21)+(IF(AJ21="STR",STR,(IF(AJ21="DEX",DEX,(IF(AJ21="CON",CON,(IF(AJ21="INT",INT,(IF(AJ21="WIS",WIS,CHA))))))))))
    try this...
    =SUM(AM21:BB21)+INDIRECT(AJ21)

    I will look areound and see if I see more like that.

    In the mean time, where is this formula of yours (post 1) going?
    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

  5. #5
    Registered User
    Join Date
    04-16-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: trying to get a formula to quit calculating after another cell has changed

    I'm sorry that was what I was missing it is the column BE
    it is BE21 down to the end of the skills list.
    Thanks

    would you believe that I have not been formally taught excel. just asked people or googled
    Last edited by weedfiend; 04-16-2013 at 01:08 PM.

  6. #6
    Registered User
    Join Date
    04-16-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: trying to get a formula to quit calculating after another cell has changed

    Sorry this is the formula that I should have had in the BC column. The CJ16 isn't on your copy, it is a formula that takes armor check penalties if the AJ column in skills is STR or DEX would the indirect function still work by adding in the if statement for STR or DEX

    =SUM(IF(AJ22=$BG$18,$BF$18,0),$BF$19,AM22:BB22,IF(AJ22="STR",STR+$CJ$16,IF(AJ22="DEX",DEX+$CJ$16,IF(AJ22="CON",CON,IF(AJ22="INT",INT,IF(AJ22="WIS",WIS,CHA))))))

  7. #7
    Registered User
    Join Date
    04-16-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: trying to get a formula to quit calculating after another cell has changed

    AL2 is the class level this is the cell I change manually with a dropdown
    BF21 is a list for my dropdown and cells I use in my if statements for level dependent formulas
    AH19 is the skill points you get each time you level. This cell changes when the INT modifier changes
    what I want is for the cells in BE21:BE60 to calculate the cell at that time and not calculate after the "AL2" changes.
    basically stating that you can't learn something new, in the past.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,225

    Re: trying to get a formula to quit calculating after another cell has changed

    OK so talk me through what is supposed to happen...what goes where, what changes to what, what criteria etc etc

  9. #9
    Registered User
    Join Date
    04-16-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: trying to get a formula to quit calculating after another cell has changed

    when I change AL2 from 1 to 2. I want BE21 to go from =IF($AL$2>=BF21,$AH$19*4) to 16 because that is what AH19*4 equals to.

    when I change AL2 from 2 to 3. I want BE22 to go from =IF($AL$2>=BF22,$AH$19) to 5 because that is what AH19 equals now.

    when I change AL2 from 3 to 4. I want BE23 to go from =IF($AL$2>=BF23,$AH$19) to 6 because that is what AH19 equals now.

    I know it is kinda confusing. most people I talk to about this get confused. Thank you for your time

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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