How do I allow users to make a choice in a worksheet?

[Jim Zavone]

I have a worksheet that identifies a unit description, a unit cost, a markup
percentage, a markup price and a total price from left to right across a
worksheet. The users input the unit description in cell A(x) using drop down
list thru data validation. The users input the unit cost in cell B(x). Then
the users enter a markup percentage in cell C(x). Cells D(x) and E(x) are
protected formulas that calculate the markup amount and the total price. I
need to give the users the choice in how they markup the unit cost either by
percentage or a flat dollar amount. Which ever the user chooses, I would
need the other to populate in it's cell and possibly also be protected,
therefore the user can only make one choice. After the choice, the totaling
column also needs to calculate the respective value. Any suggestions--Thanks

 

[Ron Coderre]

Try this:
The markup value:
Select C2
Format>Cells>Number
Category: Custom
Type: [<=1.5]0.00%;[>1.5]$0.00;General
Click [OK]

Then the markup amount:
D2: =IF(C3<=1.5,B3*C3,C3)

The Total Price
E2: =+B3+D3

By doing the above, if a user enters a value less than or equal to 1.5 as a
markup...it is assumed to be a percent. Amounts above 1.5 are assumed to be
dollars.

Does that work for you?

Regards,
Ron

 

[Jim Zavone]

Ron,

That doesn't work because users will use percentages greater that 1.5 I was
looking more along the lines of the option button in the control toolbar.
Giving the users a choice to make and then depending on which option they
choose the appropriate cells would be activated and use the appropriate
formula.