Holding formula between sheets when copying cells or inserting rows

[KWTStan]

Hi all, I'm trying to hold a formula in one worksheet so that when data is dragged or copied in a reference sheet it doesn't throw up a !REF! error. The formula I have is this 

=IF('Action Plan'!B3="Y", 'Action Plan'!A3,""). Which just copies the text across if a "Y" is entered in the B column. This formula is set up for the top 100 rows which should be plenty.

I can't lock the cells to prevent rows being inserted as I have a dynamic filter working on the sheet (to keep everything neat and tidy) I can't seem to make the INDIRECT function work/not sure if this is even the correct way of attempting this.

Any suggestions?

[#2]

@KWTStan The type of formula you mention allows the referenced cells to be dragged, row/columns inserted/deleted. But you can't delete or over-write the cells. Then you will get the #REF! error. 

Can you upload an example file to demonstrate the problem?

[#3]

@Riny_van_Eekelen my apologies you are correct I hadn't realised it was only this process that affected it. Is there a way to solve the overwrite/delete issue? I'm trying to make the spreadsheet as useable as possible by multiple people so that it can't be broken in this way.

[#4]

@KWTStan Can't really tell without seeing the schedules you are working with.

[#5]

@Riny_van_Eekelen Uploaded here. The aim is to draw information from one sheet to the next without having to duplicate it. Thanks.

[#6]

@KWTStanYou can protect the sheet and make sure that delete rows/columns are NOT checked, unlike in picture below (done on a Mac but similar on PC). If needed, put a password on the sheet.

[#7]

@Riny_van_Eekelen 

Thanks a lot though unfortunately I can't use this solution in my scenario because I am running a macro that allows filters to update automatically between sheets and if I protect the sheet from adding or deleting rows then this presents a conflict.