Excel - disable formula
Hi,
I have a worksheet where amounts are entered in columns A1 through A10. I don't want users to be able to enter formulas in this range. Is there a way to prevent user to enter the
= sign and type a formula?
conernesto
I have a worksheet where amounts are entered in columns A1 through A10. I don't want users to be able to enter formulas in this range. Is there a way to prevent user to enter the
= sign and type a formula?
conernesto
ASKER
I don't want to lock the cells as I need user to enter information. Can the formula be disabled for the range of cells?
If you want users to enter data, then you should not have formulas. The formulas should be used to manage the data entry.
So it sounds to me like what you want is a column for data entry and another column for formulas (which can be locked since they should be read-only.)
If you want to lock a specific group of cells, just select the entire sheet and in the Format Cells' Protection tab, uncheck "Locked" and click OK.
then go back to the cells you'd like to lock (whatever range) and select Locked.
Finally, go to Home tab's Cells group, Format item and select Protect Sheet. On the window that appears, uncheck "Select locked cells" so users can only access the unlocked cells.
(If you need to modify something there, go back to the same spot and "Unprotect Sheet")
So it sounds to me like what you want is a column for data entry and another column for formulas (which can be locked since they should be read-only.)
If you want to lock a specific group of cells, just select the entire sheet and in the Format Cells' Protection tab, uncheck "Locked" and click OK.
then go back to the cells you'd like to lock (whatever range) and select Locked.
Finally, go to Home tab's Cells group, Format item and select Protect Sheet. On the window that appears, uncheck "Select locked cells" so users can only access the unlocked cells.
(If you need to modify something there, go back to the same spot and "Unprotect Sheet")
ASKER
I don't have formulas on the input column Cells A1 to A10. However, the user can enter the equal sign (=) and enter a formula on any of this rows. I want user to be able to enter say 100 but not =50+50.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another way could be to add some auto-macro code so that when a user leaves a cell, it removes the equal sign at the beginning or otherwise manages user input.