CODEWITHSUNDEEP
excelformula
Adam Reece
Adam Reece

Reputation: 23

Excel: Adding a row disrupts formula

In a spreadsheet I use for cash management tracking, I have the following formula: =IF(D176="Cash", F175+C176, IF(D176="Transfer", F175+C176, F175))

When I add a row, I use control+D to fill in the formula from the cell above (I'm using Excel for Mac 2011). This results in the correct formula as follows: =IF(D177="Cash", F176+C177, IF(D177="Transfer", F176+C177, F176))

However, this has the effect of changing the formula in the cell in the row below: =IF(D178="Cash", F176+C178, IF(D178="Transfer", F176+C178, F176))

Here you can see the rows for column F are not correct: F176 should be F177.

Can anyone offer any advice to ensure that when I insert a row the formula remains intact?

Thanks.

Upvotes: 0

Views: 99

Answers (1)

user4039065
user4039065

Reputation:

Replace all of the references to F175 in the original formula (the one if row 176) with INDEX(F:F, ROW()-1).

=IF(D176="Cash", INDEX(F:F, ROW()-1)+C176, IF(D176="Transfer", INDEX(F:F, ROW()-1)+C176, INDEX(F:F, ROW()-1)))
'or better as
=INDEX(F:F, ROW()-1)+(OR(D176={"Cash", "Transfer"}*C176)

Upvotes: 2

Related Questions