disallow formulas

**maacmaac** · 12-13-2009, 01:08 AM

Is there a way to disallow users from using fromulas in a spreadsheet? I have an issue in which users are using formulas to concat data as a shortcut to typing in data but they are constantly referencing the incorrect cells. Thanks for any comments.

**chrns12** · 12-13-2009, 01:33 AM

You can either format the cells as text or go to Excel Options->Display Options for this Worksheet->"Show Formulas in cells instead of their calculated results"

EDIT: The second method is for Excel 2007 - I'm not sure where this option is in Excel 2003

**maacmaac** · 12-13-2009, 02:01 AM

I'm using 2003. Not quite what I was looking for. I think this would confuse the user even more. Instead of seeing the value in the cell, they would now see the formula. I am looking for a way to prevent them from entering the formula (i.e. if the first character in the cell is "=", then disallow entry). Thanks.

**mikerickson** · 12-13-2009, 02:28 AM

I think that Excel savvy users are my allies against the poorly trained users.
I would hesitate before taking a useful a tool, like formulas, away from my allies.

If you put this in the sheets code module, the user won't be able to enter a formula.

Please Login or Register  to view this content.

**maacmaac** · 12-13-2009, 02:40 AM

Ok, that works. Thanks for the code. BTW, I am taking your advice about taking away the formula functionality from some of the users but I can't think of any other way to get around the issue without having to redesign the entire spreadsheet.

**mikerickson** · 12-13-2009, 03:06 AM

If you lock your formula cells and protect the worksheet, the worst the user can do is enter bad data.

Spreadsheet design cannot prevent GIGO.

But if some of the cells can be derived by formula from others cells (apparently some of your users think this is so), why not put the proper formulas in place, so the user's not tempted to "reference the incorrect cells".

**maacmaac** · 12-13-2009, 09:36 AM

I thought of putting in a formula for the column that is being messed up then locking down that column but it wouldn't work in this case. The reason being is that say column C is the column the users are entering concat formulas. Column A is first name, column B is last name. I can't use a formula because sometimes the value needs to be something different than what is the concat value of columns A & B. I do appreciate your comments. Thanks.