Excel formula not updating correctly/automatically

%3CLINGO-SUB%20id%3D%22lingo-sub-1791034%22%20slang%3D%22en-US%22%3EExcel%20formula%20not%20updating%20correctly%2Fautomatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791034%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3CBR%20%2F%3EI%20am%20having%20an%20issue%20with%20the%20attached%20spreadsheet%20that%20the%20formulas%20in%20the%20tax%20and%20insurance%20schedules%20are%20not%20updating%20correctly%20unless%20I%20click%20in%20to%20the%20formula%20in%20the%20cell%20and%20hit%20enter.%20I%20have%20already%20checked%20and%20the%20sheets%20are%20all%20set%20to%20Automatic%20calculations%20so%20that%20is%20not%20the%20issue.%20The%20formulas%20still%20calculate%20when%20I%20change%20one%20of%20the%20cells%20being%20checked%20in%20the%20if%20statement%20(E17%2C%20for%20example)%20but%20the%20formula%20is%20now%20applying%20to%20the%20wrong%20cell%20unless%20I%20click%20on%20it%20and%20hit%20enter.%20I%20have%20attached%20a%20test%20spreadsheet%20for%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20the%20formula%20to%20do%20the%20in%20the%20sections%20%22Tax%20Schedule%22%20(A54-A69)%20and%20%22Insurance%20Schedule%22%20(G54-G69)%20is%20if%20the%20month%20is%201%20less%20than%20the%20month%20of%20the%20next%20disbursement%20(E17%2C%20E18%2C%20E19%2C%20and%2For%20E20)%20then%20the%20month%20needs%20to%20show%20up%202%20times%20in%20a%20row%20in%20the%20date%20column%20(ie.%20if%20next%20disbursement%20is%205%2F1%2F2021%2C%20then%20April%202021%20should%20be%20on%20the%20schedule%20twice).%20Currently%2C%20if%20the%20disbursement%20date%20in%20cells%20E17-E20%20are%20changed%2C%20the%20formula%20seems%20to%20set%20the%20duplicating%20date%20back%202%20months%20instead%20of%201%20unless%20I%20manually%20update%20the%20formula.%20I%20have%20a%20similar%20formula%20in%20the%20schedules%20in%20the%20%22payment%22%20column%20(column%20B)%20that%20works%20correctly%20even%20when%20I%20change%20the%20disbursement%20date%20but%20column%20A%20refuses%20to%20update.%20Any%20help%20would%20be%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1791034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1791078%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20not%20updating%20correctly%2Fautomatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F835573%22%20target%3D%22_blank%22%3E%40redzone7827%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20circular%20references%20in%20formulas%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20204px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227250i948709EAD2AA32BE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20them%20result%20is%20always%20unpredictable.%20As%20first%20formulas%20shall%20be%20cleaned%20from%20such%20references%2C%20and%20only%20after%20that%20check%20if%20result%20correct%20or%20not.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!
I am having an issue with the attached spreadsheet that the formulas in the tax and insurance schedules are not updating correctly unless I click in to the formula in the cell and hit enter. I have already checked and the sheets are all set to Automatic calculations so that is not the issue. The formulas still calculate when I change one of the cells being checked in the if statement (E17, for example) but the formula is now applying to the wrong cell unless I click on it and hit enter. I have attached a test spreadsheet for reference.

 

What I need the formula to do the in the sections "Tax Schedule" (A54-A69) and "Insurance Schedule" (G54-G69) is if the month is 1 less than the month of the next disbursement (E17, E18, E19, and/or E20) then the month needs to show up 2 times in a row in the date column (ie. if next disbursement is 5/1/2021, then April 2021 should be on the schedule twice). Currently, if the disbursement date in cells E17-E20 are changed, the formula seems to set the duplicating date back 2 months instead of 1 unless I manually update the formula. I have a similar formula in the schedules in the "payment" column (column B) that works correctly even when I change the disbursement date but column A refuses to update. Any help would be appreciated!

2 Replies

@redzone7827 

You have circular references in formulas

image.png

with them result is always unpredictable. As first formulas shall be cleaned from such references, and only after that check if result correct or not.

Ah..ok, I didn't realize it showed that there, thank you! I'll look at trying to remove the circular reference and post again if I have any issues. Thank you!