Holding formula between sheets when copying cells or inserting rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2569930%22%20slang%3D%22en-US%22%3EHolding%20formula%20between%20sheets%20when%20copying%20cells%20or%20inserting%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2569930%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20I'm%20trying%20to%20hold%20a%20formula%20in%20one%20worksheet%20so%20that%20when%20data%20is%20dragged%20or%20copied%20in%20a%20reference%20sheet%20it%20doesn't%20throw%20up%20a%20!REF!%20error.%20The%20formula%20I%20have%20is%20this%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF('Action%20Plan'!B3%3D%22Y%22%2C%20'Action%20Plan'!A3%2C%22%22).%20Which%20just%20copies%20the%20text%20across%20if%20a%20%22Y%22%20is%20entered%20in%20the%20B%20column.%20This%20formula%20is%20set%20up%20for%20the%20top%20100%20rows%20which%20should%20be%20plenty.%3C%2FP%3E%3CP%3EI%20can't%20lock%20the%20cells%20to%20prevent%20rows%20being%20inserted%20as%20I%20have%20a%20dynamic%20filter%20working%20on%20the%20sheet%20(to%20keep%20everything%20neat%20and%20tidy)%20I%20can't%20seem%20to%20make%20the%20INDIRECT%20function%20work%2Fnot%20sure%20if%20this%20is%20even%20the%20correct%20way%20of%20attempting%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2569930%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2570170%22%20slang%3D%22en-US%22%3ERe%3A%20Holding%20formula%20between%20sheets%20when%20copying%20cells%20or%20inserting%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570170%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1108879%22%20target%3D%22_blank%22%3E%40KWTStan%3C%2FA%3E%26nbsp%3BThe%20type%20of%20formula%20you%20mention%20allows%20the%20referenced%20cells%20to%20be%20dragged%2C%20row%2Fcolumns%20inserted%2Fdeleted.%20But%20you%20can't%20delete%20or%20over-write%20the%20cells.%20Then%20you%20will%20get%20the%20%23REF!%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20upload%20an%20example%20file%20to%20demonstrate%20the%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2570196%22%20slang%3D%22en-US%22%3ERe%3A%20Holding%20formula%20between%20sheets%20when%20copying%20cells%20or%20inserting%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2570196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bmy%20apologies%20you%20are%20correct%20I%20hadn't%20realised%20it%20was%20only%20this%20process%20that%20affected%20it.%20Is%20there%20a%20way%20to%20solve%20the%20overwrite%2Fdelete%20issue%3F%20I'm%20trying%20to%20make%20the%20spreadsheet%20as%20useable%20as%20possible%20by%20multiple%20people%20so%20that%20it%20can't%20be%20broken%20in%20this%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

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

@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.

Screenshot 2021-07-21 at 16.17.01.png

 

@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.