+ Reply to Thread
Results 1 to 16 of 16

EOMonth issue

  1. #1
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Unhappy EOMonth issue

    I have a looping formula in the Amort Tab. Cell F35, L35, M35, B36 and R36.

    The whole page was working fine until 2 days ago. I don't know what I did as technically the formulas are all copied from the one above it.

    I'm trying to connect real time data (Tab Final) with the Amortization schedule in real time so it is always up to date. And because of the interest setup with the bank I have to calculate in per day (Amort Columns P, Q, R and S) then charge it at the end of the month and even then it varies with the bank. Hence Amort Column M which should = Final Column I. I do think the loop has something to do with Amort Column M but it works in Cells M22, M25, M28 and M31 so I don't know why it doesn't work further down the column.
    Attached Files Attached Files
    Last edited by wildecoyote1966; 01-02-2022 at 04:57 AM. Reason: changing title and question

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: looping formula

    The formulas are quite interactive and confusing, but I'm sure you have a typo in your formula in column K. Within it, it says ...IF(18="",... Well, the number 18 will never equal blank :-) (maybe you meant I8??)

    I'm continuing to look to see about the circular reference

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: looping formula

    Cell M35 looks at F35, which looks at a range in R (of which one of them is cell R36), which looks at B36, which looks at L35, which looks at M35, which I believe completes the circle.

  4. #4
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Re: looping formula

    Quote Originally Posted by Gregb11 View Post
    The formulas are quite interactive and confusing, but I'm sure you have a typo in your formula in column K. Within it, it says ...IF(18="",... Well, the number 18 will never equal blank :-) (maybe you meant I8??)

    I'm continuing to look to see about the circular reference
    Thank you. Actually meant column B

  5. #5
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Re: looping formula

    Quote Originally Posted by Gregb11 View Post
    Cell M35 looks at F35, which looks at a range in R (of which one of them is cell R36), which looks at B36, which looks at L35, which looks at M35, which I believe completes the circle.
    This is so confusing. Why did it work before

  6. #6
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    EOMonth issue

    let me see if I can explain what I want

    in Cell B54 (Amort tab) it is showing the same date as B53.

    B53 is showing B52+14 which is the End of the month.
    My issue is I have a repayment and Interest on the same day so I need to provide for that. so on 30/06/2022 (Cell B53 and B54) I have a "Repayment" due and also the bank charges "Interest" on the same day.

    Cell C52 =IF(A53="","",IF(B53=Final!B58,Final!C58,IF(B53<>EOMONTH(B53,0),"Repayment","Interest"))) displayed "Interest"

    I changed to =IF(A53="","",IF(B53=Final!B58,Final!C58,IF(B52+14=EOMONTH(B53,0),"Repayment","Interest"))) displayed "Repayment."
    However when I copied it to the next line it showed Interest. I'm thinking I need to look at the date and the details. ie. look at the 2 dates and say if the details = "Repayment" then "Interest"

    I'm not sure
    Attached Files Attached Files
    Last edited by wildecoyote1966; 12-31-2021 at 10:33 AM. Reason: Adding more information

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: EOMonth issue

    I feel that you only need to look at Date, i.e., for cells B20 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Re: EOMonth issue

    Thanks that seems to be working great.

    You made that look simple. I tried these really long formulas.

  9. #9
    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: EOMonth issue

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

    Also, if you have not already done so, 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 all those who offered help.

    For future reference: thread titles are meant to be descriptive of the issue you are trying to resolve, not the name of a function that isn't working as expected. Please try to make your thread titles more informative in future, as per the forum rules. Thanks.
    Last edited by AliGW; 01-02-2022 at 04:46 AM.
    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.

  10. #10
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    locking cells after a date

    Can I lock a cell after say a date

    so I want to lock cells in Column H once the date in C1 is past the date in the corresponding cell in Column A. ie. H25 and A25
    Attached Files Attached Files

  11. #11
    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: EOMonth issue

    Not without VBA. This is a new question, so please start a new thread with a DESCRIPTIVE title in the VBA section. This thread should be marked as solved. Thanks.

  12. #12
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Re: EOMonth issue

    I can't find the tools.

  13. #13
    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: EOMonth issue

    What tools???

    I have asked you to pose your new question by starting a new thread in the correct forum section here: https://www.excelforum.com/excel-pro...ng-vba-macros/

    There's a button at the top that says '+ Post New Thread'.

    I have given instructions about marking this thread as solved in post #9 above, along with advice on acceptable thread titles.

  14. #14
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Re: EOMonth issue

    I know you said to close it. which I have done now. I was trying to find where to do that.

  15. #15
    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: EOMonth issue

    OK, thanks for clarifying - I've provided a link to the VBA section above for you.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: EOMonth issue

    Re: Post #8
    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Help with looping formula needed
    By dangorka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2019, 01:03 PM
  2. Looping a Formula?
    By danhenshy23 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-29-2016, 02:22 PM
  3. [SOLVED] Is there a way to put the vlookup formula without looping involve?
    By emina002 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-27-2014, 10:54 PM
  4. looping a formula
    By ceejay22 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2013, 07:25 PM
  5. Looping Formula Help
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2013, 05:56 AM
  6. Looping through cells setting a formula
    By JonPugh in forum Excel General
    Replies: 1
    Last Post: 01-08-2010, 12:04 PM
  7. Looping of repeatitive formula
    By Chetan Chauhan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2009, 03:03 AM

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