Can you lock a cell or prevent it from being edited with a formula?

[bryanr72]

Greetings again-


I've run into a situation that was submitted to me, by my boss. I'm creating a Concrete Mix Design, and there are certain ACI & ASTM Standards we have to go by to calculate how much cement, water, & fly ash have to be added to the aggregate.

We also have to include "adjustment" cells, so that if the contractor decides he wants to add more cement than the "Standard", we will be able to adjust it w/o re-writing the formula. Well, if he makes an adjustment, then it no longer meets the "Standards".

On my Data Sheet, I will have a drop-down box that asks if the design meets "ACI Standards". If 'YES' is chosen, then a title under the heading will appear and say: "THIS MIX DESIGN MEETS ACI STANDARDS" And, of course, if 'NO' is chosen, then the title will not show up.

OK, so here is my question:

If 'YES' is selected, I would like to lock the adjustment cells, so that you don't accidentally say it meets standards, and then still be able to put a value in the adjustment cell.....which would throw it out of standards.

Is that even possible? Just in case it isn't, is there a formula to change the text color in a cell, or change the fill color in a cell, or finally a formula to automatically change a value to "0" if 'YES' is selected and leave the value alone if 'NO' is selected?


Have I thoroughly confused you yet? Sorry, for the long ramble, and all the run-on sentences.


Any help is appreciated. Thank you in advance.


-Bryan

[stephenloky]

You need a macro to do that.

[GeneralDisarray]

Another way to go, is to just alter how the formulas use those adjustment cells.



Here's the basic idea. Where ever you have the formula, you would need to do this:

=CHOOSE((SelectionCell="yes")*1+1, normal_Formula_here, formula_ignoring_adjustment_cells_here)

So, you would need one version of the formula that makes use of the adjustment cells and another version that ignores them. You would then "choose" which to use based on whether or not they say "yes" or "no" in the other cell.


If this is not clear, please post an example for more help

[bryanr72]

I have attached a file, to better help with understanding my issue. If you have any questions, please ask.


-Bryan

[GeneralDisarray]

Just replace the formulas.

In cell C12: =CHOOSE(1*(D5="YES")+1,((((+C8*94)+C13))-((((C8*94)+C13)*C18)/1.2)),((((+C8*94)))-((((C8*94))*C18)/1.2)))

In cell C16:=CHOOSE((D5="YES")*1+1,((+C8*94)*C18)+C17,((+C8*94)*C18))


What this will do, is provide two formulas to use for the calculations. The "YES" version of the formula will just ignore the adjustment values.

[bryanr72]

Thank you for the help, but I can't quite do it like that. All of that data is referenced on a MIX DESIGN REPORT, so even if the calculation ignores some of the values, they will still appear on the report.

I think I'm gonna try to learn some kind of complicated IF/THEN (AND) statement. Instead of where I had the YES/NO menu in D5, I'm gonna try to say something like this: "IF the SAND ADJUSTMENT cell =0, AND the CEMENT ADJUSTMENT cell =0, AND the FLY ASH ADJUSTMENT cell =0, AND the WATER ADJUSTMENT cell =0, AND the WATER/CEMENT RATIO cell does not say "CUSTOM", THEN say "YES" in D5, otherwise say "NO".

Can you help me with a bunch of IFs, THENs, & ANDs??

[gak67]

Try this in D5:

Please Login or Register  to view this content.

1 IF statement and 1 AND statement with multiple conditions.

[GeneralDisarray]

If you want those numbers to not appear in a report, then you can control the formatting of the cells containing the numbers.

You can make the Font color (for the adjustment cells) match the background color - so they will appear blank - when "YES" is selected. That will keep them from appearing on the report.

So to sum up, the solution i would suggest is: use CHOOSE() to keep those cells out of the formulas and conditional formatting to keep them from appearing in the report - that would allow you to complete this without macros.

----

See attachment for your preferred solution.

With macros you can use the protected area idea you started with, but you need to alter your understanding just a bit. You will have to define all the areas the user can edit and not the areas you want to protects i.e., you don't identify what NOT to protect and everything else is off limits.

To make the unlocked area change with the selection of "YES" or "NO" you will need to use a worksheet_change() event like this:


This solution requires named ranges to identify the unlocked areas, and a worksheet_change event macro to control what is locked depending on a selection cell (which is also a named range ACI_Choice).

Please Login or Register  to view this content.

[bryanr72]

Thank you for everyone's help. gak67's IF statement will work best. Rather than locking cells, I will allow changes to be made in the adjustment cells, which will make D5 say no....and the heading that will say, "THIS DESIGN MEETS ACI STANDARDS" will not appear. If conditions are correct, then it will appear.

I did learn something new with GeneralDisarray's formulas, so that is a bonus. Thanks!!


-Bryan

[GeneralDisarray]

No problem. Please mark your thread "[SOLVED]"