Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Reference - Why?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Reference - Why?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Reference - Why?
On Jan 2, 1:33*pm, Alan Smith
wrote: 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 ToolsFormula AuditingTrace 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.) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Reference - Why?
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. On Jan 2, 4:33*pm, 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Reference - Why?
for visible cells only.
The formula is calculating filtered data. -- Biff Microsoft Excel MVP "iliace" wrote in message ... 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. On Jan 2, 4:33 pm, 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Reference - Why?
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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Reference - Why?
Are you sure you're opening the same file?
Maybe you have two files with the same name in different folders? Alan Smith wrote: 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" wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Reference - Why?
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 "Dave Peterson" wrote: Are you sure you're opening the same file? Maybe you have two files with the same name in different folders? Alan Smith wrote: 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" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular Reference | Excel Discussion (Misc queries) | |||
Circular reference help! | Excel Discussion (Misc queries) | |||
circular reference when using a UDF | Excel Worksheet Functions | |||
how to: circular reference | Excel Worksheet Functions | |||
Circular reference | Excel Discussion (Misc queries) |