Weird insert line problem...

[SamAshN]

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?

[austenr]

Please post a sample workbook.

[SamAshN]

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

Thanks.

[shrivallabha]

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

[SamAshN]

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?

[SamAshN]

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!

[shrivallabha]

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,