How to fix reference cells so that not affected by changes to the reference

I have the below formula in a column that refer to another worksheet:
e.g.
...
=IF('NameList'!J40="","",'NameList'!J40)
=IF('NameList'!J41="","",'NameList'!J41)
=IF('NameList'!J42="","",'NameList'!J42)
=IF('NameList'!J43="","",'NameList'!J43)
...

But when I do update/changes to the list, e.g. insert row 41, my table will become
...
=IF('NameList'!J40="","",'NameList'!J40)
=IF('NameList'!J42="","",'NameList'!J42)
=IF('NameList'!J43="","",'NameList'!J43)
...

and all new data inserted in between existing rows are not able to be captured by the formula.

May I know what changes or settings do I need to do?

thank you.

You could avoid inserting rows into your reference data, and add new data to the end of the existing data.

OR, you could just copy the first of those formulae down after you have inserted or deleted some rows.

OR, you could change the formula to this:

=INDEX('NameList'!J:J,ROWS($1:40))&""

and copy this down.

OR, you could change the formula to one that uses INDIRECT, but as that is a volatile function you may see performance issues.

Hope this helps.

Pete

Pete, thanks for the various options. I would avoid inserting rows in between myself, but I am not sure who else will be using this table. So I am trying my best to make it more flexible
For the formula, it works, but I have some other cells that is vlookup to this column and apparently they do not work well with each other.

TMS, Bigroo,
Thanks for the lead, I am still exploring the structured table option. After some quick try, am I right to say that this structured table only works for formula within the same worksheet?