+ Reply to Thread
Results 1 to 5 of 5

Formulas Disappearing after Cell reference changes

  1. #1
    Forum Contributor
    Join Date
    07-22-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    122

    Formulas Disappearing after Cell reference changes

    I have a formula that updates based on other cells, but with any changes to the cell references the formual is removed.


    Cells involved:
    A1 = 15000.00
    B1 = 9.00%
    C1 = 60
    D1 = abs(pmt(B1/12,C1,A1) (example the value returned is $311.38)
    D2 = Optional Payment or the real payment amount if the estimate is wrong from D1 (example is $350.00)
    D3 = Other Payments (example is $50.00)
    D4 = D2 + D3
    E1 = IF(D2>0,D4,D1+D3) basically stating that if a actual/other payment actually exists, then if uses the acual payment, but if the estimate is correct it will add the other payment to the estimate regardless is the other payment exists or has a value, so the variations can be actual/est + other even if 0.00/estimate.

    When the formual works if the information is not present, but when making changes to different values for A1:C1, the formula is E1 is completely removed and defaults to just D4, the formula in D4 does not change nor remove itself. I have tried creating a new sheet but it ends up having the same issue, there are no macros that effect E1, only cells that will pull the info for their own calculations which those work with no issues.

    Is there a setting i am missing?
    Any help would be appreciated. Thanks in advance
    Last edited by paxile2k; 12-23-2010 at 09:38 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formulas Disappearing after Cell reference changes

    post the affected workbook
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    07-22-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    122

    Re: Formulas Disappearing after Cell reference changes

    Quote Originally Posted by martindwilson View Post
    post the affected workbook
    On an updated note, i used both the click a different cell and enter to get out of the effected cell and both have the same issue, but i used the formual if a different cell, and it works fine, with no issues. Is there a restriction/setting/format that could be effecting the cell?

    I would post the workbook, but it does have sensitive data.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formulas Disappearing after Cell reference changes

    set up another workbook with the data you have above does it work?

  5. #5
    Forum Contributor
    Join Date
    07-22-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    122

    Re: Formulas Disappearing after Cell reference changes

    Quote Originally Posted by martindwilson View Post
    set up another workbook with the data you have above does it work?
    I did, but i think i found the issue, there is a userform that i thought was removed, that had the cell listed as a controldata source, i removed the datasource reference, and it is working, then replaced the datasource, with a textbox reference on the userform activate sub, to have the textbox = the cell

    Thankyou for your assitance, it was greatly appreciated, your response got me thinking on exactly is on the workbook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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