Solved

Excel - disable formula

Posted on 2013-06-07
5
172 Views
Last Modified: 2013-06-17
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
0
Comment
Question by:Conernesto
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 39230809
One way it so lock those cells, which means to unlock all cells that you want to allow data entry into, then turn on protection.

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.
0
 

Author Comment

by:Conernesto
ID: 39235203
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?
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39235287
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")
0
 

Author Comment

by:Conernesto
ID: 39235573
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.
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 39235692
ah, ok...I guess the easiest way is to format those cells as Text.  Select them then in the Format Cells window, "Number" tab (or directly in the Number group's Number Format item), change the Category to "Text".

It won't prevent entering an "=" but it will prevent Excel from evaluating it.

If you want to prevent it entirely, you'll need VBA, which can do a lot more but will also be more complicated to create if you want to, for example, skip all special characters like "+".
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now