If formula not updating cell reference when insert row

Cross-posted at https://www.mrexcel.com/board/thread...t-row.1195229/

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.

Where are you inserting a new row?

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

I suspect that you need to adjust the first formula, assuming it is in a Structured Table, so that it takes into account the row.

No - please post a workbook that you have cut down and desensitised to show the issue.

Sorry again. I'm completely new to posting on the forum. See attached sample sheet.

Try this In AH3 copy down

Please Login or Register  to view this content.

Try this also.

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have added the link to post #1 because you are a new member. Please become familiar with the rules before posting again.

Can you give what formula you want to have in the file.