Freezing Formulas

[Barbara]

Novice using excel. I am making a price sheet and want to freeze the
formulas but still be able to enter quanities and have it calculate.

Example 43 times 15.00 = 645.00 Formulas are working great I just don't
want the people using them to be able to change the formulas. I have gone
through the help and followed the instructions but it won't let me put in
quantities after I do that.

I had to take the protection off to be able to use my price sheet and that
results in the formulas still able to be lost or changed.

Thank you in advance for any help

 

[Bill Ridgeway]

I always colour code cells containing formulae. This doesn't fully protect
them from being corrupted and, unless the colour is changed, may not look
right on a print out. Failing any other suggestion it is, at least, a
warning that the cell should not be tampered with.

Regards.

Bill Ridgeway
Computer Solutions

 

[kassie]

You would normally set up your sheet to have a column for eg part number, a
column for a description, and a column with unit prices. No need to add
quantities on a pricelist.

You could then set up a quotation sheet, which will draw info from this
pricelist. This sheet will contain the same columns as aforesaid, as well as
a column for quantities and a column for extended prices. While the part
number column is unprotected, the other columns - the ones holding the
formulae, should be protected.

Let's say you have this in a file called Price List.xls, set up as follows:
Col A Part #, Col B Description, Col C Price, starting in row 2, down to 150

If you have your part number in A, say in A10, then in B10 you could insert
a formula to look up the description - =IF(A10="","",VLOOKUP(A10,'[Price
list.xls]Sheet1'!$A$2:$C$150,2,FALSE))

In C10 you would enter a formula - =IF(A10="","",VLOOKUP(A10,'[Price
List.xls]Sheet1'!$A$2:$C$150,3,FALSE)) - to look up the unit price

In D10 you would enter the quantity quoted on

In E10 you would enter the formula =IF(D10="","",C10*D10)

You then protect columns B,C and E.

 

[Gord Dibben]

By default all cells are locked when the sheet is protected.

Hit CRTL + A(twice if xl2003) then Format>Cells>Protection. Uncheck "locked"
and OK.

Select the cells you wish to lock and Format>Cells>Protection. Check "locked"
and OK.

Now Tools>Protection>Protect Sheet. This is mandatory!!

You can set a password to unprotect. These can easily be broken in Excel but
will keep your formulas from being accidentally overwritten.


Gord Dibben Excel MVP

Novice using excel. I am making a price sheet and want to freeze the
formulas but still be able to enter quanities and have it calculate.

Example 43 times 15.00 = 645.00 Formulas are working great I just don't
want the people using them to be able to change the formulas. I have gone
through the help and followed the instructions but it won't let me put in
quantities after I do that.

I had to take the protection off to be able to use my price sheet and that
results in the formulas still able to be lost or changed.

Thank you in advance for any help

Gord Dibben MS Excel MVP