+ Reply to Thread
Results 1 to 2 of 2

Protecting against inadvertent corruption of formula references when cut/paste other cells

  1. #1
    Registered User
    Join Date
    10-27-2014
    Location
    UK
    MS-Off Ver
    Office 10
    Posts
    22

    Protecting against inadvertent corruption of formula references when cut/paste other cells

    Hi,

    I'm aware that if you cut & paste cells that are referenced by formulae in other cells, the formulae references themselves are automatically updated. This is true even if the cells containing the formula are 'locked' and worksheet itself is protected. [The cells being cut/pasted are not locked - probably because they are 'data input' cells].

    I normally protect against this eventuality by including a little VBA code to disable 'cutting' of cells, and requiring users to 'copy', 'paste' and 'delete' if they want to effectively achieve a 'cut and paste' functionality.
    But this solution requires making all my workbooks macra-enabled.

    Is there an equally secure/safe method of protecting the integrity of my cell formula without making my workbooks macro-enabled?


    Thanks in anticipation for any good solutions. I have searched the web extensively.

    MathUKTeacher

  2. #2
    Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    364

    Re: Protecting against inadvertent corruption of formula references when cut/paste other c

    You could add Data Validation.

    Highlight the range of cells you don't want people to change, select the Data Ribbon, Data Validation, select 'Whole Number, enter a start range eg: 50 and end range 51. Select the error Alert and type in a warning message. This will stop users from changing the cells unless they enter 50.5.

    nb: Anyone with any knowledge of Excel can defeat this if they choose too by clearing the validation, but the message and cell entry prevention will stop most people.
    Uploading an attachment will assist Forum Members in finding the correct Solution for you. Create a dummy worksheet if necessary, add your start data and show where / what your expected results should be. TO ADD A FILE:

    Go to 'Advanced. click on 'Manage Attachments. Select 'Browse & Choose your file, Select 'Upload and then close the Attachment window.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1