Getting rid of a circular reference error message

I have a workbook (attached to the message so take a peek) with a circular reference that, apparently, has got to be there. I added a private sub to make sure the sheet keeps the ITERATION checkbox selected:

Please Login or Register  to view this content.

One problem still is that the sub apparently goes into effect AFTER the workbook checks for circular references. What happens is that the person opening the workbook gets the "excel cannot do this" message that is rather unsightly and potentially confusing to the user.

How do I:

A. Get rid of (supress?) the warning box that appears or
B. Have the sub get called upon BEFORE the workbook checks for circular references?
C. Figure out a way to avoid the circular reference altogether.

Anything would be good at this point. Please help!

No one brave enough to take a stab at this?

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
[email protected]
[email protected] with @tiscali.co.uk
"sharkfoot" <[email protected]> wrote
in message news:[email protected]...
>
> I have a workbook (attached to the message so take a peek) with a
> circular reference that, apparently, has got to be there. I added a
> private sub to make sure the sheet keeps the ITERATION checkbox
> selected:
>
>
> Code:
> --------------------
> Private Sub Workbook_Open()
> Application.Iteration = True
> End Sub
> --------------------
>
>
> One problem still is that the sub apparently goes into effect AFTER the
> workbook checks for circular references. What happens is that the person
> opening the workbook gets the "excel cannot do this" message that is
> rather unsightly and potentially confusing to the user.
>
> How do I:
>
> A. Get rid of (supress?) the warning box that appears or
> B. Have the sub get called upon BEFORE the workbook checks for circular
> references?
> C. Figure out a way to avoid the circular reference altogether.
>
> Anything would be good at this point. Please help!
>
>
> +-------------------------------------------------------------------+
> |Filename: lease.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4516 |
> +-------------------------------------------------------------------+
>
> --
> sharkfoot
> ------------------------------------------------------------------------
> sharkfoot's Profile:
> http://www.excelforum.com/member.php...o&userid=32164
> View this thread: http://www.excelforum.com/showthread...hreadid=525887
>

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?

Originally Posted by Sandy Mann

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
[email protected]
[email protected] with @tiscali.co.uk

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
[email protected]
[email protected] with @tiscali.co.uk


"sharkfoot" <[email protected]> wrote
in message news:[email protected]...
>
> 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?
>

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
[email protected]
[email protected] with @tiscali.co.uk
"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "sharkfoot" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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?
>>
>
>
>

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!

Originally Posted by Sandy Mann

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
[email protected]
[email protected] with @tiscali.co.uk

Hi Shankfoot,

Assuming that you will have a similar setup in the other two tables as you
have in column D so that the cells that end up with circular references are:
D20, H20 & L20 then it should be fairly simple .

You will notice have that the formula that is in H20 only referred to Column
H so when it was copied to D20 it then only referred to Column D. This
means that we can copy the formula to N20 and use that one formula to copy
to cells D20, H20 &L20.

With Macros disabled, (otherwise they may change things back after you
change them), I unhid Column H, copied the formula in H20 to N20,(or you can
just type into N20 the forumula =N12-N18 Note that the foumula MUST refer to
the same column that the formula is in and the cell MUST be formatted the
same as you want cells D20, H20 & L20 to be because when the Macro pastes in
the new formula the formatting will automatically be changed to that of cell
N20. Also note that you don't need a formula in P20 or R20.), I then hid
Column N.

Next I changed the CalculateIt() Macro to:

Sub CalculateIt()

With Sheets("Lease Worksheet")
.Range("N20").Copy .Range("D20")
.Range("D20").Copy
.Range("D20").PasteSpecial Paste:=xlValues

.Range("N20").Copy .Range("H20")
.Range("H20").Copy
.Range("H20").PasteSpecial Paste:=xlValues

.Range("N20").Copy .Range("L20")
.Range("L20").Copy
.Range("L20").PasteSpecial Paste:=xlValues

Application.CutCopyMode = False

End With

End Sub

I tested it by creating two other tables with the calculations in Columns D,
H & L and all three tables updated as expected. I found by experimentation
that the .Calculate line that was in my original code was not required -
Excel calculates when the formulas are pasted in - although I am sure that
it wouldn't do so when I was originally trying out the code.

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk

"sharkfoot" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>> [email protected]
>> [email protected] 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
>

Originally Posted by Sandy Mann

Hi Shankfoot,

Assuming that you will have a similar setup in the other two tables as you
have in column D so that the cells that end up with circular references are:
D20, H20 & L20 then it should be fairly simple .

Thank you so much. This works perfectly. And the way you explain things helps me learn much better than someone just fixing the problem for me. Thanks again!

You're welcome Sharkfoot,

"sharkfoot" <[email protected]> wrote
in message news:[email protected]...

> Thank you so much. This works perfectly. And the way you explain things
> helps me learn much better than someone just fixing the problem for me.

"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...

> H & L and all three tables updated as expected. I found by
> experimentation
> that the .Calculate line that was in my original code was not required -
> Excel calculates when the formulas are pasted in

Seems like we both learned for the experience.

--
Regards

Sandy
[email protected]
[email protected] with @tiscali.co.uk