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
"Sandy Mann" wrote in message
...
Hi Shankfoot,

What is wrong is that the formula you have in the hidden H20 is reading
=SUM(D12-D18) when it should be referencing H12 & H18.

I fixed it by:

Open the file with Macros disabled, unhide Column H and change the formula
in H20 to =H12-H18 (The SUM part is not required.)

Hide Column H again and put any number into D20. This will remove all the
#REF! errors.

Save the spreadsheet under another name and close it. Now open the new
spreadsheet again and enable Macros. After that it worked for me again.

(Incidentally the SUM part is not required in D12 either or you can change
it to =SUM(D6:D11), similarly D26 & D32 don't require a SUM either).

If you have any more trouble the do post back again.

--
HTH

Sandy

with @tiscali.co.uk


"sharkfoot" wrote
in message ...

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?