I have created a Sales Inventory Spreadsheet to help me as I put projects together that has entry fields for part number, vendor, descripiton, quantity, cost and sale price. In addition I have three columns with formulas for Extended Cost, Extended Sale and Profit Margin. This all works fine. I then modified the "sale price" to allow me to enter a number into a "Desired Margin" (DM) field and if something is entered in that field, the sale price is calculated.

=IF(DM0,(E4/(1-DM)), )

The ??

is there a way to protect the "Sell Price" field if a value is entered in DM and remove protection if the DM field is cleared?

Les

Hi Les,

You could do this with VBA or if you want a weaker version you could use
Data, Validation.

Select the cell where the Sales Price calculation is and choose Data,
Validation, Custom. Enter a formula such as:

=IF(DM<"",A10<A10,)

Here I assume that the cell containing the DM entry if any is named DM.
Cell A10 is the location of the Sales Price calculation you want to protect.

--
Cheers,
Shane Devenshire


"Les Linton" wrote:


I have created a Sales Inventory Spreadsheet to help me as I put
projects together that has entry fields for part number, vendor,
descripiton, quantity, cost and sale price. In addition I have three
columns with formulas for Extended Cost, Extended Sale and Profit
Margin. This all works fine. I then modified the "sale price" to
allow me to enter a number into a "Desired Margin" (DM) field and if
something is entered in that field, the sale price is calculated.

=IF(DM0,(E4/(1-DM)), )

The ??

is there a way to protect the "Sell Price" field if a value is entered
in DM and remove protection if the DM field is cleared?

Les




--
Les Linton

Thanks for the help Shane. I will try that on my next one. Knowing that my sales people can break anything, I simply added another column protected my formula and now they can do it either way.

Les