Circular?

[Dean]

I have inherited a file that was set to iterative mode and I turned that off, then tried to find the circular references, as I do not like iterative approaches in very complex workbooks, since I feel they can 'solve their way' through programming errors. In any event, on one of the sheets, the cell that is identified as circular shows no dependents when I do trace dependents. Isn't that impossible?

Thanks for any help.
Dean

[Dave Peterson]

I'd bet that if excel thinks that there's a circular reference, there's a
circular reference.

But if you have trouble finding the circular reference, maybe Stephen Bullen's
utility will help:

http://www.oaltd.co.uk/Excel/Default.htm
Look for FindCirc.zip

> Dean wrote:
>
> I have inherited a file that was set to iterative mode and I turned that off,
> then tried to find the circular references, as I do not like iterative
> approaches in very complex workbooks, since I feel they can 'solve their way'
> through programming errors. In any event, on one of the sheets, the cell that
> is identified as circular shows no dependents when I do trace dependents.
> Isn't that impossible?
>
> Thanks for any help.
> Dean

--

Dave Peterson

[Dean]

It didn't find it either. It looks like this cell is a function of cell C,
where the obvious circularity is a chain from cell A to cell E, where A
depends on B which depends on C ... which depends on E, which depends back
on A.

So, though C is in the middle of that chain, I still wouldn't expect it to
cause a dependent cell, which is not in that chain, to show as circular,
would you?

In other words, if I change cell A to a number, all circularity goes away.

Thanks!
D



DO

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I'd bet that if excel thinks that there's a circular reference, there's a
> circular reference.
>
> But if you have trouble finding the circular reference, maybe Stephen
> Bullen's
> utility will help:
>
> http://www.oaltd.co.uk/Excel/Default.htm
> Look for FindCirc.zip
>
>> Dean wrote:
>>
>> I have inherited a file that was set to iterative mode and I turned that
>> off,
>> then tried to find the circular references, as I do not like iterative
>> approaches in very complex workbooks, since I feel they can 'solve their
>> way'
>> through programming errors. In any event, on one of the sheets, the cell
>> that
>> is identified as circular shows no dependents when I do trace dependents.
>> Isn't that impossible?
>>
>> Thanks for any help.
>> Dean
>
> --
>
> Dave Peterson

[Dave Peterson]

If you change A1 to a number and the circular reference warning goes away, then
look at the cells that are referred to in A1 (and the references in those
references in those references).

It sure sounds like a circular reference to me.

Dean wrote:
>
> It didn't find it either. It looks like this cell is a function of cell C,
> where the obvious circularity is a chain from cell A to cell E, where A
> depends on B which depends on C ... which depends on E, which depends back
> on A.
>
> So, though C is in the middle of that chain, I still wouldn't expect it to
> cause a dependent cell, which is not in that chain, to show as circular,
> would you?
>
> In other words, if I change cell A to a number, all circularity goes away.
>
> Thanks!
> D
>
> DO
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > I'd bet that if excel thinks that there's a circular reference, there's a
> > circular reference.
> >
> > But if you have trouble finding the circular reference, maybe Stephen
> > Bullen's
> > utility will help:
> >
> > http://www.oaltd.co.uk/Excel/Default.htm
> > Look for FindCirc.zip
> >
> >> Dean wrote:
> >>
> >> I have inherited a file that was set to iterative mode and I turned that
> >> off,
> >> then tried to find the circular references, as I do not like iterative
> >> approaches in very complex workbooks, since I feel they can 'solve their
> >> way'
> >> through programming errors. In any event, on one of the sheets, the cell
> >> that
> >> is identified as circular shows no dependents when I do trace dependents.
> >> Isn't that impossible?
> >>
> >> Thanks for any help.
> >> Dean
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Dean]

Hi Dave,

I'm a little confused. The trace circular tells me exactly the chain, it identifies cell A, which depends on B .... D, which depends on E, which depends on cell A, an obvious circular loop. Obviously, if I break the chain anywhere between A and E, the circularity goes away, including that of "Joe" (defined later).

The cell that I call C is in the middle of that chain and the cell (let's call it "Joe") on another worksheet that shows as circular was the only remaining issue. I guess Joe is, at best, only second generation circular, in that it depends on a cell (cell C) that is in the chain. But what confuses me most is that this Joe shows no dependents and I have no doubt that is true, since it is on a results summary exhibit, not to mention that nothing changes at all if I delete Joe.

Is the answer that EXCEL gets a little confused in the face of circularity, sort of a guilt by association?

Thanks!
Dean

"Dave Peterson" <[email protected]> wrote in message news:[email protected]...
> If you change A1 to a number and the circular reference warning goes away, then
> look at the cells that are referred to in A1 (and the references in those
> references in those references).
>
> It sure sounds like a circular reference to me.
>
> Dean wrote:
>>
>> It didn't find it either. It looks like this cell is a function of cell C,
>> where the obvious circularity is a chain from cell A to cell E, where A
>> depends on B which depends on C ... which depends on E, which depends back
>> on A.
>>
>> So, though C is in the middle of that chain, I still wouldn't expect it to
>> cause a dependent cell, which is not in that chain, to show as circular,
>> would you?
>>
>> In other words, if I change cell A to a number, all circularity goes away.
>>
>> Thanks!
>> D
>>
>> DO
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > I'd bet that if excel thinks that there's a circular reference, there's a
>> > circular reference.
>> >
>> > But if you have trouble finding the circular reference, maybe Stephen
>> > Bullen's
>> > utility will help:
>> >
>> > http://www.oaltd.co.uk/Excel/Default.htm
>> > Look for FindCirc.zip
>> >
>> >> Dean wrote:
>> >>
>> >> I have inherited a file that was set to iterative mode and I turned that
>> >> off,
>> >> then tried to find the circular references, as I do not like iterative
>> >> approaches in very complex workbooks, since I feel they can 'solve their
>> >> way'
>> >> through programming errors. In any event, on one of the sheets, the cell
>> >> that
>> >> is identified as circular shows no dependents when I do trace dependents.
>> >> Isn't that impossible?
>> >>
>> >> Thanks for any help.
>> >> Dean
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Dave Peterson]

First, this is a plain text newsgroups. Most people don't like HTML posts (and
binary attachments).

I've never seen excel confused about circular references. But I have seen me
struggle to find them.

(I'd keep looking <bg>.)


> Dean wrote:
>
> Hi Dave,
>
> I'm a little confused. The trace circular tells me exactly the chain, it
> identifies cell A, which depends on B .... D, which depends on E, which
> depends on cell A, an obvious circular loop. Obviously, if I break the chain
> anywhere between A and E, the circularity goes away, including that of "Joe"
> (defined later).
>
> The cell that I call C is in the middle of that chain and the cell (let's
> call it "Joe") on another worksheet that shows as circular was the only
> remaining issue. I guess Joe is, at best, only second generation circular, in
> that it depends on a cell (cell C) that is in the chain. But what confuses me
> most is that this Joe shows no dependents and I have no doubt that is true,
> since it is on a results summary exhibit, not to mention that nothing changes
> at all if I delete Joe.
>
> Is the answer that EXCEL gets a little confused in the face of circularity,
> sort of a guilt by association?
>
> Thanks!
> Dean
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > If you change A1 to a number and the circular reference warning goes away,
> then
> > look at the cells that are referred to in A1 (and the references in those
> > references in those references).
> >
> > It sure sounds like a circular reference to me.
> >
> > Dean wrote:
> >>
> >> It didn't find it either. It looks like this cell is a function of cell C,
> >> where the obvious circularity is a chain from cell A to cell E, where A
> >> depends on B which depends on C ... which depends on E, which depends
> back
> >> on A.
> >>
> >> So, though C is in the middle of that chain, I still wouldn't expect it to
> >> cause a dependent cell, which is not in that chain, to show as circular,
> >> would you?
> >>
> >> In other words, if I change cell A to a number, all circularity goes away.
> >>
> >> Thanks!
> >> D
> >>
> >> DO
> >>
> >> "Dave Peterson" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > I'd bet that if excel thinks that there's a circular reference, there's a
> >> > circular reference.
> >> >
> >> > But if you have trouble finding the circular reference, maybe Stephen
> >> > Bullen's
> >> > utility will help:
> >> >
> >> > http://www.oaltd.co.uk/Excel/Default.htm
> >> > Look for FindCirc.zip
> >> >
> >> >> Dean wrote:
> >> >>
> >> >> I have inherited a file that was set to iterative mode and I turned that
> >> >> off,
> >> >> then tried to find the circular references, as I do not like iterative
> >> >> approaches in very complex workbooks, since I feel they can 'solve their
> >> >> way'
> >> >> through programming errors. In any event, on one of the sheets, the
> cell
> >> >> that
> >> >> is identified as circular shows no dependents when I do trace
> dependents.
> >> >> Isn't that impossible?
> >> >>
> >> >> Thanks for any help.
> >> >> Dean
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Dave Patrick]

Isn't it possible the circular reference is wholly contained within the cell
formula?

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Dave Peterson" wrote:
| First, this is a plain text newsgroups. Most people don't like HTML posts
(and
| binary attachments).
|
| I've never seen excel confused about circular references. But I have seen
me
| struggle to find them.
|
| (I'd keep looking <bg>.)
|
|
| > Dean wrote:
| >
| > Hi Dave,
| >
| > I'm a little confused. The trace circular tells me exactly the chain,
it
| > identifies cell A, which depends on B .... D, which depends on E, which
| > depends on cell A, an obvious circular loop. Obviously, if I break the
chain
| > anywhere between A and E, the circularity goes away, including that of
"Joe"
| > (defined later).
| >
| > The cell that I call C is in the middle of that chain and the cell
(let's
| > call it "Joe") on another worksheet that shows as circular was the only
| > remaining issue. I guess Joe is, at best, only second generation
circular, in
| > that it depends on a cell (cell C) that is in the chain. But what
confuses me
| > most is that this Joe shows no dependents and I have no doubt that is
true,
| > since it is on a results summary exhibit, not to mention that nothing
changes
| > at all if I delete Joe.
| >
| > Is the answer that EXCEL gets a little confused in the face of
circularity,
| > sort of a guilt by association?
| >
| > Thanks!
| > Dean
| >
| > "Dave Peterson" <[email protected]> wrote in message
| > news:[email protected]...
| > > If you change A1 to a number and the circular reference warning goes
away,
| > then
| > > look at the cells that are referred to in A1 (and the references in
those
| > > references in those references).
| > >
| > > It sure sounds like a circular reference to me.
| > >
| > > Dean wrote:
| > >>
| > >> It didn't find it either. It looks like this cell is a function of
cell C,
| > >> where the obvious circularity is a chain from cell A to cell E, where
A
| > >> depends on B which depends on C ... which depends on E, which
depends
| > back
| > >> on A.
| > >>
| > >> So, though C is in the middle of that chain, I still wouldn't expect
it to
| > >> cause a dependent cell, which is not in that chain, to show as
circular,
| > >> would you?
| > >>
| > >> In other words, if I change cell A to a number, all circularity goes
away.
| > >>
| > >> Thanks!
| > >> D
| > >>
| > >> DO
| > >>
| > >> "Dave Peterson" <[email protected]> wrote in message
| > >> news:[email protected]...
| > >> > I'd bet that if excel thinks that there's a circular reference,
there's a
| > >> > circular reference.
| > >> >
| > >> > But if you have trouble finding the circular reference, maybe
Stephen
| > >> > Bullen's
| > >> > utility will help:
| > >> >
| > >> > http://www.oaltd.co.uk/Excel/Default.htm
| > >> > Look for FindCirc.zip
| > >> >
| > >> >> Dean wrote:
| > >> >>
| > >> >> I have inherited a file that was set to iterative mode and I
turned that
| > >> >> off,
| > >> >> then tried to find the circular references, as I do not like
iterative
| > >> >> approaches in very complex workbooks, since I feel they can 'solve
their
| > >> >> way'
| > >> >> through programming errors. In any event, on one of the sheets,
the
| > cell
| > >> >> that
| > >> >> is identified as circular shows no dependents when I do trace
| > dependents.
| > >> >> Isn't that impossible?
| > >> >>
| > >> >> Thanks for any help.
| > >> >> Dean
| > >> >
| > >> > --
| > >> >
| > >> > Dave Peterson
| > >
| > > --
| > >
| > > Dave Peterson
|
| --
|
| Dave Peterson

[Dave Peterson]

Like putting
=A1
in A1

or
=a1+b1+a1+c1
in A1

But I would think/hope that this would be easier to find <vbg>.


Dave Patrick wrote:
>
> Isn't it possible the circular reference is wholly contained within the cell
> formula?
>
> --
>
> Regards,
>
> Dave Patrick ....Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
>
> "Dave Peterson" wrote:
> | First, this is a plain text newsgroups. Most people don't like HTML posts
> (and
> | binary attachments).
> |
> | I've never seen excel confused about circular references. But I have seen
> me
> | struggle to find them.
> |
> | (I'd keep looking <bg>.)
> |
> |
> | > Dean wrote:
> | >
> | > Hi Dave,
> | >
> | > I'm a little confused. The trace circular tells me exactly the chain,
> it
> | > identifies cell A, which depends on B .... D, which depends on E, which
> | > depends on cell A, an obvious circular loop. Obviously, if I break the
> chain
> | > anywhere between A and E, the circularity goes away, including that of
> "Joe"
> | > (defined later).
> | >
> | > The cell that I call C is in the middle of that chain and the cell
> (let's
> | > call it "Joe") on another worksheet that shows as circular was the only
> | > remaining issue. I guess Joe is, at best, only second generation
> circular, in
> | > that it depends on a cell (cell C) that is in the chain. But what
> confuses me
> | > most is that this Joe shows no dependents and I have no doubt that is
> true,
> | > since it is on a results summary exhibit, not to mention that nothing
> changes
> | > at all if I delete Joe.
> | >
> | > Is the answer that EXCEL gets a little confused in the face of
> circularity,
> | > sort of a guilt by association?
> | >
> | > Thanks!
> | > Dean
> | >
> | > "Dave Peterson" <[email protected]> wrote in message
> | > news:[email protected]...
> | > > If you change A1 to a number and the circular reference warning goes
> away,
> | > then
> | > > look at the cells that are referred to in A1 (and the references in
> those
> | > > references in those references).
> | > >
> | > > It sure sounds like a circular reference to me.
> | > >
> | > > Dean wrote:
> | > >>
> | > >> It didn't find it either. It looks like this cell is a function of
> cell C,
> | > >> where the obvious circularity is a chain from cell A to cell E, where
> A
> | > >> depends on B which depends on C ... which depends on E, which
> depends
> | > back
> | > >> on A.
> | > >>
> | > >> So, though C is in the middle of that chain, I still wouldn't expect
> it to
> | > >> cause a dependent cell, which is not in that chain, to show as
> circular,
> | > >> would you?
> | > >>
> | > >> In other words, if I change cell A to a number, all circularity goes
> away.
> | > >>
> | > >> Thanks!
> | > >> D
> | > >>
> | > >> DO
> | > >>
> | > >> "Dave Peterson" <[email protected]> wrote in message
> | > >> news:[email protected]...
> | > >> > I'd bet that if excel thinks that there's a circular reference,
> there's a
> | > >> > circular reference.
> | > >> >
> | > >> > But if you have trouble finding the circular reference, maybe
> Stephen
> | > >> > Bullen's
> | > >> > utility will help:
> | > >> >
> | > >> > http://www.oaltd.co.uk/Excel/Default.htm
> | > >> > Look for FindCirc.zip
> | > >> >
> | > >> >> Dean wrote:
> | > >> >>
> | > >> >> I have inherited a file that was set to iterative mode and I
> turned that
> | > >> >> off,
> | > >> >> then tried to find the circular references, as I do not like
> iterative
> | > >> >> approaches in very complex workbooks, since I feel they can 'solve
> their
> | > >> >> way'
> | > >> >> through programming errors. In any event, on one of the sheets,
> the
> | > cell
> | > >> >> that
> | > >> >> is identified as circular shows no dependents when I do trace
> | > dependents.
> | > >> >> Isn't that impossible?
> | > >> >>
> | > >> >> Thanks for any help.
> | > >> >> Dean
> | > >> >
> | > >> > --
> | > >> >
> | > >> > Dave Peterson
> | > >
> | > > --
> | > >
> | > > Dave Peterson
> |
> | --
> |
> | Dave Peterson

--

Dave Peterson

[Dave Patrick]

Yes, exactly.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Dave Peterson" wrote:
| Like putting
| =A1
| in A1
|
| or
| =a1+b1+a1+c1
| in A1
|
| But I would think/hope that this would be easier to find <vbg>.