Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent Formula Changes (Excel 97)

    We have a worksheet to insert daily sales figures into during the course of
    a month. Is there a way to lock the formulas in a cell so that if someone
    enters the wrong info they don't erase the formulas along with the bad info?
    Thanks,
    Jeff

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent Formula Changes (Excel 97)

    The simple answer is NO, you can't prevent formulas from being overwritten IF the user enters bad data. If the sheet is protected, then the user is prevented from changing the cell whether or not it has a formula in it. If a given cell's protection is set to 'unlock' then that cell can be changed even if its sheet is protected. (The default cell protection is 'locked' so you need to explicitly change it to unlock for this to happen.)

    In Excel XP (2002) there are more cell protection choices but that won't help you in Excel 97. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

    It sounds like you would allow the user to over-write a formula cell IF it matches some criteria. I'm trying to read between the lines of your post since you mentioned 'bad data' and I assume you want to allow the cell to be over-written if the user's data is good. Is this true? Under what conditions is it ok for your user to over-write these formula cells?

    You can limit what a user enters in a cell with Data Validation (from the Data menu) but this might not be sophisticated enough for your purposes.

    Unless you write some code, there's no way to restore the formulas to these cells in case they get over-written. In the work I've done, I added a button on the sheet called "Restore Default" which lets the user change certain unprotected cells at their leisure (so they can enter their custom data). If they then want to restore the data to its default state (meaning restore the default data that the model originally used) they click the button and the code then replaces the user's data with the default data. Typically I create a hidden worksheet that contains this default data (which can be data or formulas).

    Does any of this help you? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Deb

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent Formula Changes (Excel 97)

    You can protect the formulas so that no one can change them in Excel 97.
    Select the cells that you want to change and UNLOCK them - select Format->Cells->Protection->Unlock cells (I'm going from memory here so the command names might be a little different)
    Then protect your worksheet (Tools->Protection->Protect Sheet [or workbook if you prefer]) Add a password if you want to abolutely prevent anyone changing the formulas.
    Now your users can only change the cells that you unlocked. The formulas cannot be over-written.

    Hope this helps!

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent Formula Changes (Excel 97)

    Yes, this is one of the things I mentioned in my response but the way the poster wrote his question, it sounded like under some conditions it was OK for the user to over-write the cell. I may of read too much into his post. <img src=/S/groan.gif border=0 alt=groan width=16 height=15>
    We'll find out more later when/if he responds to our replies.

    Thnx,
    Deb <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  5. #5
    New Lounger
    Join Date
    Sep 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent Formula Changes (Excel 97)

    Ted's answer looks like the answer I need. Simply put, when people enter lots of numbers , there's going to be a few errors when keying in the data. Unfortunately, in excel when you erase a bad datum, you also erase the underlying formula.

    Thanks,
    Jeff

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Prevent Formula Changes (Excel 97)

    Erasing data does NOT erase a formula. CUTTING will break a link to the formula, MOVING a cell will keep the formula based on the NEW location, but deleting the contents will NOT affect the formula. There are ways to fix the formula to be based on a location if desired (using INDIRECT, using named formulas, etc)

    I am not sure what the underlying problem is. My "gut" response is LOCK the formulas and UNLOCK the DATA input, then protect the sheet. Tell your users NOT to MOVE cells if that is an issue or if it is an issue make sure you work around it.
    Steve

Posting Permissions

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