how to hide and protect formula in cells
Posted by elizabeth chase on August 22, 2001 8:50 AM
how to hide and protect formulas in a cell(s) also i would like to construct a formula for to compute commission for the following example
A B C D E F
COST# INV$ MARKUP% DIS% EXT.$ COMMISSION
$800 $960 20% 5% $912.00 =? should be
E1*1.5% but how
would the cell
dectect the markup
range
if the markup is >16% and over 1.5% commission
if the markup is >10% to 15% .05% commission
if the markup is less than 10% 0.00%
Posted by Tom Urtis on August 22, 2001 10:13 AM
Hi Elizabeth --
Regarding the formula to determine commission, you could use a VLOOKUP function but first you'd need to set up the VLOOKUP table:
In cell J1, enter the number 0
In J2 enter .1
In J3 enter .16
In cell K1 enter 0.
In K2 enter .05.
In K3 enter 1.5.
In cell F2 (and copy down as needed), enter the formula =VLOOKUP(C2,$J$1:$K$3,2). That will display the commission based on the markup in column C. My guess is that someone later will want to see the actual commission amount, which you might end up placing in a formula for in column G, which would be F2 times whatever the commission is based on (presumably Extended column E or whatever your company does).
Regarding protecting and hiding the formula, highlight the cells containing the formula(s), then click on Format > Cells > Protection tab, and select (put a check mark in) both the Locked and Hidden boxes. Then protect the sheet by going to Tools > Protection > Protect sheet, accept the defaults, and enter your password if you want to have one. If you do enter a password you will be prompted again to verify it by re-entering it. Don't forget your password or you won't be able to unprotect your own sheet afterwards.
HTH.
Tom Urtis
Posted by DarrenS on August 22, 2001 10:43 AM
1st question
For the cells you want to hide the formula of change their properties to hidden in the format cells | protection tab then tools | protection | protect sheet
2nd question
I don't know how to use a formula to do what you require but the code below in a macro will do what you require. I have tested on the sample data as shown below.
Sub UpdateCommissionValue()
CommissionHighScale = 16
CommissionHighRate = 1.5
CommissionMiddleScale = 10
CommissionMiddleRate = 0.5
CommissionLowScale = 0
CommissionLowRate = 0
'Select starting cell
Range("C2").Select
'Work through entire row until no value is found - assumes end of data
While ActiveCell.Value <> ""
'Determine commission rate to be applied
Select Case ActiveCell.Value
Case Is > CommissionHighScale
'Calculate commission
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 2).Value / 100 * CommissionHighRate
Case Is > CommissionMiddleScale
'Calculate commission
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 2).Value / 100 * CommissionMiddleRate
Case Is > CommissionLowScale
'Calculate commission
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 2).Value / 100 * CommissionLowRate
End Select
'Select next cell to determine if should continue
ActiveCell.Offset(1, 0).Select
Wend
End Sub
Test Data
Cost Inv$ Markup% Dis% Ext$ Commission
800 60 16 5 912 4.56
800 60 17 5 912 13.68
800 60 11 5 912 4.56
800 60 10 5 912 0
800 60 9 5 912 0
800 60 20 5 912 13.68
I have no doubt that someone will come up with a formuala to use in a cell but should they not you could use the above, it works.
