Is there a way to protect a formula?

We have a worksheet with two columns that contain formulas that are dependent on each other because this is how orders are placed.

If a customer orders cartons of an item, then we enter the carton quantity in column F and it calculates eaches in column G, but often a customer orders eaches we enter the eaches quantity in column G which calculates the quantity of cartons in column F. An order very often will contain a combination of cartons and eaches. This adds up cartons, eaches, and numerous other things to create an estimate of weights and measures for shipping purposes. (See attached example).

(Yes...I know this creates a circular reference but I've already allowed for iterations)

The problem is that the user often puts a quantity in the wrong column thus wiping out the formula. Is there a way to delete/change a quantity without affecting the formula?

Thanks in advance!!!

In your spreadsheet, all the cells are locked except the ones with formulas in them. Why not lock the ones with formulas in them?

Because protecting the sheet will protect the formula but also prevent us from entering quantities.

Thank you!

Well you already have a Ctn Qty in column F in the spreadsheet. Isn't that where you input the quantity? That column can be unlocked (select column, press CTRL+1, go to the Protection tab and make sure "Locked" is not checked).
There is no way to have a cell with a formula in it, then type over the formula in the cell and still have a formula.
One workaround is if you make your table into an actual table (Insert->Table). Add any formulas you need and make sure they apply to the entire column (after you type the formula in and press ENTER, it should prompt you to select this). This way, any new lines you add to the table will have the formula applied automatically, even if some previous lines have hard coded data (so if you added Item 5 in your cell A7, line 7 will also be incorporated in the table, meaning any formulas applied to the column will appear in the new line; a user can type over a formula but a new line would still have the formulas applied). Not sure if that makes sense to you or if it's what you want. Your description says you have formulas in columns F and G but your spreadsheet shows G is completely blank so I am confused.

My mistake; column G has a formula so sorry for the confusion! So there is no way to protect a formula in a cell so that if the value entered is deleted then the formula reappear? Just trying to "idiot proof" this darn thing.

Thank you!

Hi,how about using the attached sheet?
When you open it, you'll see 3 coloured areas, the password for each area is just under it.
The sheet has 3 areas of protection in 2 layers.

Layer 1 - Individual locked ranges
layer 2 - Overall sheet protection

Area 1 - Purple - locked, password set as 'Test'
Area 2 - Blue - locked, password set as 'Test2'
Area 3 - Orange - locked, password set as 'Test3'

The sheet is something i came up with a long time ago and posted it on this forum (somewhere)

See if it helps.

Rich

Originally Posted by ChemistB

The other option is to use VBA to reenter the formula whenever the cell is blank.
(that would require that the user enable macros).

Well, we can do this! Just am not a VBA person but know enough to be dangerous . Can you help with this?

Thank you!

If it does work for you, then view the code within 'This Workbook'
For anyoen wanting the code without downloading, see below:

Please Login or Register  to view this content.

Personally, having to idiot proof my own spreadsheets, I think data validation might be an option. Have a cell where the user can type the value and an adjacent cell with data validation where the user needs to select what type of value they typed (carton, gallons, unicorns, whatever you want). You could have another set of cells like these if you need and then you can have a separate cell with a formula based on these 4 cells. Protect the formula cell and voila.

Not an option for us.

Thank you!

Originally Posted by ChemistB

substitute commas with semi-colons if your region settings requires

Not an option for us. Not even clear what this would do....

THANK YOU EVERYONE!!! REALLY GREAT IDEAS!! Going to work on a few of these to see what would work best. I always learn something new from you guys and I truly appreciate it!

Originally Posted by FDibbins

Can I ask why?

Wouldn't the network administrator have to make a global change? Getting management to ok this would be a NIGHTMARE of biblical proportions.