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

Betty Woo

New Member
Joined
Jan 19, 2007
Messages
16
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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/tip/protecting_cells_sheets_workbooks_and_files/
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

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