Unlocked Cells - Cut and Paste Errors

G

Grandma8

Board Regular
Joined
Jun 29, 2010
Messages
105
Office Version
  1. 365
Platform
  1. Windows
I created a protected worksheet with formulas in protected cells. The formula relates to data in unprotected cells. When a user uses the shortcut keys to cut and paste between unprotected cells, the formulas follow the cut and paste action.

Below is the formula in the protected. So, as an example, when the user cuts from I11 and pastes to J11, the result of the formula is #Ref!. Is there a way to protect the formula from changing when a user uses cut and paste between unprotected cells?

=IF($I11>0,"V", IF($J11>0,"S",IF($K11>0,"P",IF($L11>0,"CE",IF($M11>0,"AS",IF($N11>0,"H",IF($O11>0,"O","")))))))
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Explanation of the issue at hand: Employees use a protected Excel worksheet that is a time sheet. There are columns for personal, vacation, sick and other benefits where employees add the number of hours. On the second page of the time sheet is a summary page with columns (Monday - Friday for a two-week period). Beneath the day, the formula add P, V S, etc. based upon what they type on page 1. However, if a mistake is made and the user "cuts and pastes" from the incorrect cell to another cell, the formulas on page 2 do not update correctly. Is there something missing in the formula that would read the information that was pasted into the cell?
 
Upvote 0
Is that is a problem, one recommendation could be to protect the ENTIRE sheet, and control all data entry through an entry Form. Then VBA will unprotect the sheet, write the data from the entry Form to the worksheet, then re-protect the sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Disable Cut Copy Paste
Replies
1
Views
371
jkpieterse
jkpieterse
M
  • Solved
Need help adapting a formula
Replies
2
Views
576
mattbird
M
N
  • Question
VBA - Intersect code being re-initiated
Replies
1
Views
146
Peter_SSs
Peter_SSs
B
  • Question
Named Cell stayed put when I Cut and Paste the cell into another cell
Replies
4
Views
199
My Aswer Is This
My Aswer Is This
D
  • Solved
Copy row based on cell value and paste as text in another worksheet
Replies
2
Views
866
DH937
D

Forum statistics

Threads
1,221,564
Messages
6,160,513
Members
451,655
Latest member
rugubara

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top