Forum Discussion
Holding formula between sheets when copying cells or inserting rows
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?
6 Replies
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?
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.
KWTStan Can't really tell without seeing the schedules you are working with.
