If formula not updating cell reference when insert row

[Jfinity]

Hi all,

I'm a new member so forgive me if this has been answered elsewhere but I have scoured the internet in search of an answer to my problem but I am yet to come up with a solution.

Basically the problem is this. I have an IF formula that starts in AH4 that runs the length of Column AH from rows 4 to the last row. See below.

=IF(IF(AND(D4="",N4=""),"",IF(D4<>"",D4,AH3))="Type","",IF(AND(D4="",N4=""),"",IF(D4<>"",D4,AH3)))

This formula works fine for what I need it to do and updates all the cells referenced in it as it moves down the row. i.e D4 becomes D5 and AH3 becomes AH4.

The part where I seem to be coming unstuck is when I insert a row into the table.
The formula updates itself in the new row but only updates the cell references for the D cells and not the AH cell references.
For example D4 become D5 but AH3 remains as AH3 when i need to to update the reference to AH4.

At present, the only way to fix it is to click "restore to calculated column formula" and as this is a shared spreadsheet with a few people that are not so computer literate, I don't want that to be the only solution.

Could this be because the formula is in the AH column? Is this some strange quirk of excel?
Open to any suggestions or possible work around anyone may have.

Thanks in advance,
J-Finity.

 

[Jfinity]

Apologies for the images instead of an actual sheet but my workplace wont allow me to install any add in for excel so i am unable to post a minisheet.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: If formula not updating cell reference when insert row
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.