Attached is the file after I made the changes you suggested. As you can
see, something is very, very wrong but I'm not sure what. Can you tell
me what went wrong?

Sandy Mann Wrote:
Shankfoot,

The only cell that seems to have a circular reference is D20 so I
would
suggest changing the formula to a constant as follows:

First of all copy the formula in D20 and paste it into cell H20, (or
any
other cell but you will have to change the references in the Macros to
suite), then hide column H

In the This Workbook Module change your Macro to:

Private Sub Workbook_Open()
Application.Iteration = True
Application.EnableEvents = False
CalculateIt
Application.EnableEvents = True
End Sub

and add:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Iteration = False
End Sub

Next in a normal Module add:

Sub CalculateIt()
With Sheets("Lease Worksheet")
.Range("H20").Copy .Range("D20")
.Calculate
.Range("D20").Copy
.Range("D20").PasteSpecial _
Paste:=xlValues
Application.CutCopyMode = False
End With
End Sub

Right-click on the "Data Entry" tab and select "View Code" and enter in
the
sheet Module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
CalculateIt
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Next do the same to the "Lease Worksheet" and insert the same code as
in
"Data Entry"

The sheet should then calculate without any *Calculate* or *Circular
Reference* alerts

--
HTH

Sandy

with @tiscali.co.uk



+-------------------------------------------------------------------+
|Filename: lease1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4523 |
+-------------------------------------------------------------------+

--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=525887