Forum Discussion

Copper Contributor

Jul 21, 2021

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

excel

KWTStan

Copper Contributor

Jul 21, 2021

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.

Riny_van_Eekelen

Platinum Contributor

Jul 21, 2021

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

KWTStan
Copper Contributor
Jul 21, 2021
Riny_van_Eekelen Uploaded here. The aim is to draw information from one sheet to the next without having to duplicate it. Thanks.
Work Plan template.xlsm38 KB
- Riny_van_Eekelen
  Platinum Contributor
  Jul 21, 2021
  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.
  - KWTStan
    Copper Contributor
    Jul 27, 2021
    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.