Excel question

[DigiCorn]

I have a spreadsheet I'm creating in Excel to track jobs through the shop. In it I have some formulas to math for me. Problem is, it's really easy to accidentally delete or erase the formula and screw up the cell, so is there a way (without locking or requiring a password) to protect the formula inside that cell so it doesn't lose it's format and can still be typed in to? I know how to lock it, but then you can't add any new information, and the only other trick I know requires a password every time you want to add new information. Also, without the use of a 3rd party plug-in, is there a way to make a cell have a date finder calendar?

[David]

I thought you could lock cells without requiring a password.

Locking cells in Excel makes them non-editable by default. First, make sure the cells are unlocked by checking them under the Format cells menu. Then select the cell/range of cells you want to lock, locking them again under the format cells menu.
Enforce this by protecting the worksheet. Your select cells are non-editable now, without a password.

[born2print]

Can you rt. click the formula cell / format / lock
but then don't protect the whole sheet?
This does not require a PW until you want to unlock the cell.
HTH but I am no xls power user so

[DigiCorn]

Well, I don't want a cell to be uneditable, but I do. I have a formula in the cell that does a math problem. I want the user to be forced to enter a number (not a letter - I want it to return an error), but not be able to edit or delete the math formula.

[Joe]

I have a spreadsheet I'm creating in Excel to track jobs through the shop. In it I have some formulas to math for me. Problem is, it's really easy to accidentally delete or erase the formula and screw up the cell, so is there a way (without locking or requiring a password) to protect the formula inside that cell so it doesn't lose it's format and can still be typed in to? I know how to lock it, but then you can't add any new information, and the only other trick I know requires a password every time you want to add new information. Also, without the use of a 3rd party plug-in, is there a way to make a cell have a date finder calendar?

Why would anyone need to type into the cell that has the formula? If they need to change the data they should change the values of the cell the formula is based upon.

[DigiCorn]

Well, I have columns for prepress, wide format, scanning, press time and bindery. The formula in the final cell is the total hours. That one is locked and cannot be changed. In the other columns, I want the operator to enter their time in a XX.XX formatting. Right now if someone enters an "f" the cell accepts that (although in the total column I get an ERR, which is what should happen), and it displays as, "f." If you hit delete, it erases the formatting and formula.

I want to force that cell to display an ERR if anything other than a number is entered. And I want it to convert the number to a decimal (if they enter 5, I want it to display 5.00). I have 90% of it formatted correctly - just need to make it so you can only enter a number and can't delete the formatting. I have it currently defined as a number, but it's not working (see screenshots), and if you delete out the f, it blanks out the formatting.

[DigiCorn]

OK... I figured out the number part of it through Data Validation, so now I just need to protect the cell, so they can enter a value, but not delete the formatting... it still removes it if you hit backspace or delete.

[Joe]

I don't have Excel but I did the same with Libra Office and if you remove the formula because as I see it you don't need it because you can set the format for those cells like this for two decimal places.



Then under Data Validation set it like this.



If you enter a letter you will get an error. If you delete or back space it will only delete the data as there is no formula but it will not delete the data validation.

[DigiCorn]

Here's what I see:

[Joe]

Can you see these?

[DigiCorn]

Those I can see, and now the original screenshots are showing up in the previous posting too.

I tested out my Excel template and I had actually done what you posted previously, and while it does blank out the cell (It shows empty vs the 0.00 it normally displays), if you go back and try to type a letter my little error window pops up, so it works. I *think* this is all I need for now. All my drop downs work, all my cells are protected from editing, and all the math adds up. Thanks!

[Joe]

If you change your data validation to what is shown below and your cells are filled with 0.00 if the data validation fails, like if someone enters a letter, when you clear the error the cell will return the value to 0.00. It will make the cell blank if they have that cell selected and hit backspace or delete but really since the cell returns the value to 0.00 there is really no reason for them to hit backspace. Of course making anything idiot proof is impossible but that might help.