please help...protect formula but not the cell

[trhlblue]

i am new to mr. excel & need help. i will try to keep it succinct. i have a worksheet that our sales people use to price our products. the entire worksheet is locked except the quantity of products that sales enters. some of the cells have formulas so if they enter 1 quantity for a speaker then a formula automatically adds 1 quanity for a speaker bracket. however, sometimes they don't need a bracket, but if the speaker bracket cell is not protected then they will erase the formula altogether. how can i protect the formula itself but still allow sales to make changes to the cell without affecting the formula? please help...i'd be forever grateful.

Thanks

trhlblue

 

[Dufus]

trhlblue,

I think you could change the formula so it lets them decide whether or not the bracket is added automatically. For example, if I enter 1 for a speaker and three other rows are automatically populated with related supplies, then I would need three cells to tell the formula whether or not I need each of the supplies. Like this:
=IF(A2="Speaker", IF(B2="Yes",1,0),0)
Now, if I put a "Yes" in B2, I get the extra part. If I don't, it's not included.

Dufus

 

[Oaktree]

Welcome to the board.

I'm afraid what you're asking is not possible--at least, it's not possible without using VBA.

You can either:
1) Introduce VBA to your file (which may bring about issues with requiring users to enable macros, etc.)
2) Have your file calculate a "suggested" quantity for speaker brackets but require the sales person to actually enter the amount (in a different cell than where the suggestion would appear). (A different approach to a similar result is Dufus's suggestion above)

 

[Richard Schollar]

Hi Trhlblue

Welcome to the Board!

I fear I don't understand what you mean - how could the users make an adjustment to the cell containing the formula without changing the formula? Or do you mean "make an addition to the formula, but not in any way remove the original formula"? If the latter, I know of no way to do this. I would recommend therefore having another cell that could hold manual adjustments that would be incorporated within the formula cell.

Richard