Circular Reference - Why?

[Alan Smith]

I have a section in a worksheet that finds the total for each client by
revenue stream, for visible cells only. The data layout is as follows:

Column G - Client Name (rows 6-709 is the whole data set, rows 741-788 is
the summary);
Columns N-Z - Revenue Streams (Products/Services) - Gross Values
Column AA - Total of N-Z (basic Sum function)
Column AB - Ranking of values in AA741-AA788
Columns AC-AO - Revenue Streams (Products/Services) - Weighted Values

When I use the following formula in N741-Z788 everything appears to be fine:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($G$6:$G$709,ROW($G$6:$G$709)-MIN(ROW($G$6:$G$709)),0,1)),--($G$6:$G$709=$G741),N$6:N$709)

When I try to use the same formula in AC741 (the only change is to the
N$6:N$709 reference, which is now AC) I get a circular reference in N742. Any
idea why that would happen? I feel like I'm missing something easy here but I
can't see it.

Thanks in advance,

Alan

 

[Dave Peterson]

You may want to try Stephen Bullen's utility:
http://www.oaltd.co.uk/Excel/Default.htm
Look for FindCirc.zip

 

[joeu2004]

When I use the following formula in N741-Z788 everything appears
to be fine:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($G$6:$G$709,
ROW($G$6:$G$709)-MIN(ROW($G$6:$G$­709)),0,1)),
--($G$6:$G$709=$G741),N$6:N$709)

When I try to use the same formula in AC741 (the only change is
to the N$6:N$709 reference, which is now AC) I get a circular
reference in N742. Any idea why that would happen? I feel like I'm
missing something easy here but I can't see it.

I suspect there is more to this than meets the eye -- literally. I do
not get an error when I cut-and-paste the above formula into
N741:Z788, then copy-and-paste N741 into AC741.

Of course, I do not have the data or formulas in the other cells that
you have. I suspect that therein lies the problem -- perhaps a
reference to AC741. Can you find it by selecting AC741 and using
Tools>Formula Auditing>Trace Dependents?

(I have no idea why would get a circ ref "in" N742 when you copy-and-
paste the formula into AC741, if the N$ references are indeed changed
to AC$, as you say.)

 

[iliace]

I can't replicate the problem either, but I don't understand why you
have the SUBTOTAL part in there. It doesn't appear to make a
difference in any of the use cases I can come up with.

 

[T. Valko]

for visible cells only.

The formula is calculating filtered data.

--
Biff
Microsoft Excel MVP


I can't replicate the problem either, but I don't understand why you
have the SUBTOTAL part in there. It doesn't appear to make a
difference in any of the use cases I can come up with.

 

[Alan Smith]

Thanks for the replies.

I think it may just be a versioning issue. When I open Excel from my Start
menu it opens in compatability mode, and opening this file afterwards causes
errors to appear. However, without opening Excel first, and opening this file
from the Recent Documents folder, seems to be ok (so far).

Any idea why that would happen?

Alan

 

[Dave Peterson]

Are you sure you're opening the same file?

Maybe you have two files with the same name in different folders?

 

[Alan Smith]

Just the one file, but I got the circular again anyway. I ran the download
from the link you gave me and it came back empty. I think the file is
basically unstable - too much data (if there is such a thing) - the last cell
in use on this sheet is BNJ735, and everything past column M5 is a formula,
not to mention the other 60+ sheets in the file, most of which have 2 charts
on them.

I'll remove a lot of the excess and see if that helps at all.

Cheers