Results 1 to 7 of 7

Clear contents of a cell and not delete formula of cell in Excel 2000?

This is a discussion on Clear contents of a cell and not delete formula of cell in Excel 2000? within the Excel Questions forums, part of the Question Forums category; I'm using Excel 2000 (so I can't lock ranges of cells through the menu bar), am not allowed by IT ...

  1. #1
    New Member
    Join Date
    Jan 2007
    Posts
    16

    Question Clear contents of a cell and not delete formula of cell in Excel 2000?

    I'm using Excel 2000 (so I can't lock ranges of cells through the menu bar), am not allowed by IT to use macros and will be creating this worksheet for people who really don't know Excel very well.

    What I want to accomplish is to essentially create a calculator on a worksheet with a row of cells that will retain a formula even if the cell contents are deleted by the user using 'Clear Contents' or the Delete button.

    Currently, erasing the cell contents this way will result in the formula also being erased.

    This calculation task will only require one row of actual number inputting since the formula results will be hand-written onto a paper form. The user will then delete the cells and go on to the next paper form and enter the new numbers for that form.

    Presently, I have A2:E2 with column labels, A3:E3 has the formulas (and I've made the row height very small so it doesn't accidently get deleted), A4:E4 is where the numbers from the paper form will be entered and D4 and E4 is where the formula will produce the calculations.

    Now I just have to figure out how to lock the formula into A4:E4 so that the sheet users aren't constantly deleting the formula with the cell contents by the highly intuitive and vastly destructive Delete button or 'Clear Contents'.

    I'm sure the ExcelGods must have envisioned this kind of thing but I can't figure it out since I'm only slightly more aware of Excel than the eventual inputters (but smart enough to come to you).

    Heeeellpp (please)?

    Betty

  2. #2
    MrExcel MVP, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    Default Re: Clear contents of a cell and not delete formula of cell in Excel 2000?

    I'm using Excel 2000 (so I can't lock ranges of cells through the menu bar)
    Since when? I'm pretty sure Excel 2000 had sheet protection

    - Select all cells
    - Go to Format-->Cells-->Protection tab
    - Uncheck boxes for Locked and Hidden
    - Click OK until all dialogs are closed
    - Select only the cells containing the formulas
    - Format-->Cells--Protection tab
    - Check the box for Locked (and 'Hidden' if you don't want the formulas to be displayed when the cells are selected)
    - Click OK until all dialogs are closed
    - Tools-->Protection-->Protect Sheet

    Once the sheet is protected, the cell protection is activated. Users will only be able to edit the unlocked cells.

    http://spreadsheetpage.com/index.php...oks_and_files/
    Kristy

  3. #3
    New Member
    Join Date
    Jan 2007
    Posts
    16

    Default Re: Clear contents of a cell and not delete formula of cell in Excel 2000?

    Whoops.
    Last edited by Betty Woo; Apr 2nd, 2009 at 07:24 PM. Reason: Double post - my bad. Sorry. Nothing to see here... .

  4. #4
    New Member
    Join Date
    Jan 2007
    Posts
    16

    Default Re: Clear contents of a cell and not delete formula of cell in Excel 2000?

    Quote Originally Posted by Von Pookie View Post
    Since when? I'm pretty sure Excel 2000 had sheet protection

    [Snip]
    Once the sheet is protected, the cell protection is activated. Users will only be able to edit the unlocked cells.
    I tried that before and test ran it with the inputters. Invariably they'd try to 'clear contents' of the entire row and, because the columns with the formula in it are not protected, they'd get a pop-up screen saying "the the cell or chart you are trying to
    change is protected and is therefore read-only" because the protected formula cells are included in the row they're trying to delete.

    However, I guess I can just put a subtle coloured background to the protectd cells and put a note in the calculation box that's noticable and LaRgE that states that only the white cells within the calculation box can be deleted... .

    I'm sure it'll take me a few inputters bugging me about the pop-up screen but I usually get them understanding the note after a few (too many) tries.

    I was sort of hoping to avoid the tries and just make the cells nice and clean but, you know, we're working with an application's that almost a decade old and IT guys who are extremely limited to what they're allowed to do so accommodations must be made and a martini or three drunk to calm the nerves.

    This sheet protection solution works for me.

    Many thanks for the extremely quick response!

  5. #5
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Clear contents of a cell and not delete formula of cell in Excel 2000?

    am not allowed by IT to use macros
    If that's true, ask them why they spent hundreds of dollars to purchace Excel when that could get a simple SS program without macros at Wal-Mart for under $30.00!!
    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  6. #6
    New Member
    Join Date
    Jan 2007
    Posts
    16

    Default Re: Clear contents of a cell and not delete formula of cell in Excel 2000?

    Quote Originally Posted by lenze View Post
    If that's true, ask them why they spent hundreds of dollars to purchace Excel when that could get a simple SS program without macros at Wal-Mart for under $30.00!!
    lenze
    'Cause we're a government organization and we're highly standardized across the country to eliminate applications interference and it is, after all, taxpayer's money paying for this stuff (and I'm a taxpayer so I can understand).

    If the application can accomplish 90% of what's required, there's no point in asking the fair citizens to pay for 5000+ upgrades every few years.

    I'm really hoping we're near the end of the cycle for our Microsoft Office Suite 2000 applications, though. Office 2003 - here we come (eventually)!!



    In the meantime, tweaking the old stuff like this is all I can hope for.

    Thank goodness some smart posters here have loooonnnggg memories for details that may not have made the upgrades years ago.

    I humbly bow to your Excel knowledge.

  7. #7
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Clear contents of a cell and not delete formula of cell in Excel 2000?

    I don't know what government agency (level) you are working for, but I've worked with both State(MT) and Federal agencies, not to mention the Military, and they all have macros available in Excel.
    If the application can accomplish 90% of what's required, there's no point in asking the fair citizens to pay for 5000+ upgrades every few years.
    It you can do 90% of what's required without macros, you are seriously under using Excel. Remember, Excel is NOT a SS program. It is a Data Analysis Program the uses a SS as it's interface. That's its power.
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com