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

Riny_van_Eekelen

Platinum Contributor

Jul 21, 2021

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?

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.