Trying to Avoid a Circular Reference

Looking for help with a Excel problem... I use Excel 2003. This spreadsheet is used by hundreds of people who know nothing about excel. I am trying to avoid having them turn on any special excel options to avoid a circular reference.

1. They always enter the full rate in cell A2
2. If they enter a discount amount, I would like the discount % and discount mo rate to calculate.
3. If they enter a discount %, I would like the discount amount and discount mo rate to calculate.
4. If they enter a discount mo rate, I would like the discount amount and discount % to calculate.

----
Current setup
-----
A1: Full Rate
B1: Discount Amount
C1: Discount %
D1: Discounted Mo. Rate

A2: $
B2: =IF(OR(D2="$",D2=0,D2="",A2="",A2="$",A2=0),"$",A2-D2)
C2: =IF(OR(D2="$",D2=0,D2="",A2="",A2="$",A2=0),"%",1-(D2/A2))
D2: =IF(OR(B2="$",B2=0,B2="",A2="",A2="$",A2=0),"$",A2-B2)

You could use a worksheet change event. This would however rely on users enabling macros when the workbook is opened.

Using the calculations in your example and with column C formatted as a percentage:

Please Login or Register  to view this content.

In case you are unfamiliar with VBA. Hit Alt + F11 to open the the Visual Basic Editor (or select Tools - Macros - Visual Basic Editor).

Copy and paste the code into the relevant sheet in the VBA Project.

To lock the VBA Project: Right click on VBAProject and select VBAProject Properties. On the Protection tab tick Lock project for viewing and type a password. Save and close the workbook.

I have attached a workbook (without the VBA Project protected) as an example.