Cut Breaks Formulas

[rbac]

I have multiple formulas mostly vlookup based on sheet B, that use information from sheet A to generate a report.

I need to be able to update the information on sheet A without breaking the formulas on sheet B.
Every time I cut (and sometimes it seems at other random times, but I think it is the same issue) any data, it breaks any formulas I have on sheet B that refference that cell and I get a #REF! error. I even tried change the formulas to use all absolute $ $ cell references, but it did not fix it.

Is there a way to prevent this? I can't lock/protect sheet a because it needs to be edited daily base off of information the report on sheet B is generating. I also have about 1200 cells with different formulas on sheet B, each one has different cell refferences (a combination of lots of typing and dragging the formulas).

Thanks!

[Jonmo1]

I would suggest to change your process of updating Sheet A

Don't CUT from A.
Instead, just clear contents.
Then you're free to paste new information in it's place.

[rbac]

This is what I have been doing, but there are multiple people using the report and new/different people using it in the future and if someone accidently breaks it without knowing (they don't always look at the report, and a lot of the columns with the formulas are hidden so they wouldn't even realize it was broken) it is a pain to fix and re-update all of the information.

Is there no way to avoid this?

I tried

=IF($A$405=TRUE,VLOOKUP(Status!$A$17,Status!$A$17:$A$41,1,FALSE),0)

and

=IF($A406=TRUE,VLOOKUP(Status!$A18,Status!$A18:$A42,1,FALSE),0)

and

=IF(A407=TRUE,VLOOKUP(Status!A19,Status!A19:A43,1,FALSE),0)

and either way it ends up looking like this after a cut

=IF($A$406=TRUE,VLOOKUP(Status!#REF!,Status!$A$18:$A$42,1,FALSE),0)

Thanks!

[ChemistB]

I agree with Jonmo. If you want to cut row 6, Copy rows 5 down and paste in 6. Then delete the last row.

[Jonmo1]

You can protect the workbook structure.
That will prevent people from deleting rows.

This is different from Sheet protection, though it's found in the same place.
Protect Workbook Structure.

This also disallows adding/deleting sheets.

[rbac]

I just tried several different variations of locked sheet and workbook protection options, none seem to do anything useful. Either you cannot edit the cells, or cutting them still results in the problem. The contents need to be editable.

Maybe there is a way to disable cutting in the workbook, or prompt them when they push cntrl+x or right click and remind the user to not cut?

[Jonmo1]

Guess I was wrong on that.
I could have sworn protecting the stucture would prevent deleting rows.

Is it just that one formula you need to protect? (same formula filled down column counts as just 1 formula).
Or will there be several different formulas?

There are methods, but they can be 'significantly' expenseive in regards to sheet performance.
You may be better off working to educate users.

Can you not just give the users a copy of the file with the formulas replaced by their values using copy pastespecial values?
I would think if it's called a 'Report', then the information contained in the report should be STATIC, and no need to have formulas.

[rbac]

It seems it did prevent deleting rows, it would only delete the information in the row when it was locked, but cutting/pasting still broke the formulas.

Unfortunantly parts of the report, and the main point of the report is to show what is old and needs to be removed off the Status! worksheet, and what is new and needs to be added to the Status! worksheet, so it is always changing.... it seems like MS has a handy feature they could implement into Excel....

This spreadsheet isnt huge, its 3 tabs, two tabs have no formula, and are less than 150 rows total. The third tab is formulas, and is about 600 rows and 2 columns, so unless its crazy slowing down the sheet, then it shouldnt be a problem?

[Jonmo1]

So only 2 formulas (2 columns, 1 formula each filled down) ?

Post the 'working' formulas as they existed before you tried anything to protect them.

[mehmetcik]

Hi

Why don't you try this.

Record a macro to enter all the formulas into your workbook as it is.

Modify the macro to check that all the formulae are entered into the right places and to Remove all Formulas using copy paste special.

Add a button Labelled re-calculate: Or even set the macro to run whenever the sheet is changed.

This is what I used ten years ago to stop people messing up my quotation spreadsheet and also to save memory.

So if you only have two formulae in two columns then 6 lines of code would do this for you.

[mehmetcik]

Check first column row to start installing formula
Check first column row to stop installing formula
Insert formula in that range.

Same for Column 2.

[FDibbins]

I know this is not the answer you want to hear, but it is always better to train useres better, than to try and make allowances for where they will mess up. Believe me, they know FAR more ways to mess up, than you (or any1) does about correcting them....work in the old addage that prevention is better than cure

Maybe add a HUGE sign at the top saying DONT cut/paste, use COPY/paste instead

[rbac]

I know this is not the answer you want to hear, but it is always better to train useres better, than to try and make allowances for where they will mess up. Believe me, they know FAR more ways to mess up, than you (or any1) does about correcting them....work in the old addage that prevention is better than cure

Maybe add a HUGE sign at the top saying DONT cut/paste, use COPY/paste instead

Considering all the formulas in the reporting tab are different, and each reffer to different cells (there are like 180 and formulas in one column, each with 150 arguements, and and nothing is repeated at all between each formula lol) that is probably what I will just do...

Is there a way to make a popup when they select that tab that reminds them to not cut? If it popped up and they had to push ok every time they opened the tab i would hope they remember.

mehmetcik's solution seems legit, but an incredible amount of work considering how my formulas are setup, and for how little i know about VB.

[Jonmo1]

You mean you manually entered 180 different formulas in one column?
Each one with 150 arguments?
That's alot more complex than the formulas you posted in post #4
That must have taken forever.


You can make a popup window appear with some VBA Code.
Right click the sheet's tab (the sheet that users will likely use Cut and cause errors)
View code
Paste the following

Private Sub Worksheet_Activate()
MsgBox "Do Not use CUT.  Use Copy/Paste instead"
End Sub

[rbac]

You mean you manually entered 180 different formulas in one column?
Each one with 150 arguments?
That's alot more complex than the formulas you posted in post #4
That must have taken forever.


You can make a popup window appear with some VBA Code.
Right click the sheet's tab (the sheet that users will likely use Cut and cause errors)
View code
Paste the following

Private Sub Worksheet_Activate()
MsgBox "Do Not use CUT.  Use Copy/Paste instead"
End Sub

Yea because every refference in every formula was setup a bit different so there was no way to drag/copy the formula, though I am sure there is a faster way to do it with some nice VB code. The formula is like 8 lines long and I had to make my spreadsheet saved as a binary spreadsheet lol.

I will just go with the popup as my fix then I don't have to worry about any of this anymore hopefully. Thanks!