Awesome. I have one more question. I need to have this done 3 times in
this sheet. In other words, what do I need to change to have the D, H,
and L columns (we already have D working) all figure this same formula?
I have the formulas for D20 copied into N20. H20 copied into P20 and L20
copied into R20. I'm don't know if I need to add to the current module
or just add 2 more modules for each of the new target cells.

Can you tell me wat todo to get these other 2 leases working? Thanks!

Sandy Mann Wrote:
Perhaps I should have explained why the worksheet went wrong for you.

It looks like you either copied only the SUM(D12-D18) without the =
sign or
simply typed into cell H20 exactly what is in Cell D20.

If you simply click into a cell and click copy either by the toolbar
button
or the right-click menu and then click into another cell and paste
Excel
will automatically adjust the reference to the new location. For
example in
cell H2 enter the formula =D2 Now click back into the cell and copy it
and
paste into cell E2. The formula that you have just pasted into Cell E2
will
now be =A2. Excel changed the formula which was referencing a cell
four
columns to the left of the original to be still referencing a cell
four
columns to the left but not from the NEW location.

Next copy cell H2 again and now paste it into cell D2 - you will get a
#REF!
error! Why? Because it is still referencing a cell four columns to the
left
but now there is no cell four columns to the left of D2 so Excel alerts
you
to this by giving you a #REF! error.

An exception to this is when you make the reference ABSOLUTE as in
=$D$2.
This will always refer to cell D2 even if you paste it into cell A10.
Look
up *Move or copy a formula* in Help.

By copying the formula in D20 to H20 in the first place it changes it
from
=D12-D18 to =H12-H18. H12 and H18 are both empty so it will not create
a
circular reference. However, when it gets copied back into D20 by the
Macro
of course it does create the circular reference again but the Macro
goes on
to paste the contents of the cell as a constant thus removing the
circular
reference error once more.



--
HTH

Sandy

with @tiscali.co.uk



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