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

Alan Smith wrote:

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