Consulting

Results 1 to 7 of 7

Thread: Weird insert line problem...

  1. #1

    Weird insert line problem...

    Ok, so I've got a basic spreadsheet that calculates a monthly return at the end of the month:

    =IF(C12 = "", "", IF((MONTH(C12) <> MONTH(C13)), ((K13 - AC13) / AC13), ""))

    Cell C12 is blank, but every cell below it in the array has a date in descending order (newest date goes at the top, in Cell C13). So, on a new day I'd like to copy row 13, then insert the copied cells, followed by changing the old info with the latest info in the row 13. When I do this, however, the following are what I get in column M, where I'm having the problem...

    Cell 13: =IF(C11 = "", "", IF((MONTH(C11) <> MONTH(C13)), ((K13 - AC13) / AC13), ""))
    Cell 14: =IF(C12 = "", "", IF((MONTH(C12) <> MONTH(C14)), ((K14 - AC14) / AC14), ""))
    Cell 15: =IF(C14 = "", "", IF((MONTH(C14) <> MONTH(C15)), ((K15 - AC15) / AC15), ""))

    My question, why do the cells in red above do that? ie, they should be c14, c13, c12. But they skip c13, then descend. The rest of the formula shifts properly, but not this part. I have an identical problem in a different column, but all my other formulas in other cells that shift down work perfectly. What am I missing?

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location
    Please post a sample workbook.
    Peace of mind is found in some of the strangest places.

  3. #3
    Sorry, I've been busy lately. Attached is a sample of the page from my spreadsheet.

    Thanks.

  4. #4
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Strange indeed. Here is what happens as I see it:
    1. When you insert a row by selecting the topmost row as you desire. The formula does update but partially. It remains C12!
    2. That will explain the two cells offset when you paste special = formula in the new M13 cell from M14 cell. If you paste it from M15 cell, it will be as you desire.

    Couldn't figure out the problem in step 1 though. Here's what I think happens in background.
    1. As soon as you insert a new row. Excel dynamically updates the formula but thinks that the information (i.e. C12) is still at same reference (C12) and retains as it is!

    That's it for Watson. Let us wait for Sherlock Holmes
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  5. #5
    Thanks for the look. Yeah, it's stumping me... never happened before in other spreadsheets I've done. The other formulas in the row update properly, but the formula in both NEW cells M14 and P14 (don't know if you saw that one or not) don't update. Thanks again! Anyone else?

  6. #6
    I was looking over your response again, Shrivallabha, and at first thought I'd found something I'd missed the first time. In 2, you said if I copy from line 15, it will work... but it doesn't. At least, not the way I'm doing it. So I'm right clicking on the entire row 15 on the left, and selecting copy. Then I right click on the entire row 13 on the left and select "Insert Copied Cells". The results of cells M13 - M16 are as follows:

    M13: =IF(C12 = "", "", IF((MONTH(C12) <> MONTH(C13)), ((K13 - AC13) / AC13), ""))
    M14: =IF(C12 = "", "", IF((MONTH(C12) <> MONTH(C14)), ((K14 - AC14) / AC14), ""))
    M15: =IF(C14 = "", "", IF((MONTH(C14) <> MONTH(C15)), ((K15 - AC15) / AC15), ""))
    M16: =IF(C15 = "", "", IF((MONTH(C15) <> MONTH(C16)), ((K16 - AC16) / AC16), ""))

    All of the cells work properly except cell M14 (the previous M13 before the insert). It retains it's C12 reference. Am I doing something wrong? Thanks!

  7. #7
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Plain guess...the column "M" will somewhere have "!" mark in a box to indicate inconsistent formula across column. Run it to remove the inconsistency (M14).
    Regards,
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •