Why is this a circular reference?
 

Try copy and paste this to anywhere in a blank worksheet:

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))0,INDIRECT( ADDRESS(18,COLUMN()))=TRUE)

On my machine, Excel keeps saying this is a circular reference. Can anyone
explain to me why?

Why is this a circular reference?
 

hi,
guessing here but...
if references are omited from Row() or Column() then excel assumed that
reference is the row or column that the functions appears in which would
create a circular reference. seems that you will need references to distance
the formula from the data.

Regards
FSt1

"CK" wrote:

Try copy and paste this to anywhere in a blank worksheet:

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))0,INDIRECT( ADDRESS(18,COLUMN()))=TRUE)

On my machine, Excel keeps saying this is a circular reference. Can anyone
explain to me why?

Why is this a circular reference?
 

This part of the formula creates the circular reference:

INDIRECT(ADDRESS(ROW(),COLUMN()))0

It refers to the cell that the formula is in thus being a circular
reference.

Biff

"CK" wrote in message
...
Try copy and paste this to anywhere in a blank worksheet:

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))0,INDIRECT( ADDRESS(18,COLUMN()))=TRUE)

On my machine, Excel keeps saying this is a circular reference. Can anyone
explain to me why?

Why is this a circular reference?
 

Now I understand. But the interesting thing is, the formula

=INDIRECT(ADDRESS(ROW(),COLUMN()))0

actually works in conditional formatting.

"T. Valko" wrote:

This part of the formula creates the circular reference:

INDIRECT(ADDRESS(ROW(),COLUMN()))0

It refers to the cell that the formula is in thus being a circular
reference.

Biff

"CK" wrote in message
...
Try copy and paste this to anywhere in a blank worksheet:

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))0,INDIRECT( ADDRESS(18,COLUMN()))=TRUE)

On my machine, Excel keeps saying this is a circular reference. Can anyone
explain to me why?

Why is this a circular reference?
 

CF is different from a worksheet cell formula. The formula used in CF does
not actually reside in the cell like a worksheet formula.

Biff

"CK" wrote in message
...
Now I understand. But the interesting thing is, the formula

=INDIRECT(ADDRESS(ROW(),COLUMN()))0

actually works in conditional formatting.

"T. Valko" wrote:

This part of the formula creates the circular reference:

INDIRECT(ADDRESS(ROW(),COLUMN()))0

It refers to the cell that the formula is in thus being a circular
reference.

Biff

"CK" wrote in message
...
Try copy and paste this to anywhere in a blank worksheet:

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))0,INDIRECT( ADDRESS(18,COLUMN()))=TRUE)

On my machine, Excel keeps saying this is a circular reference. Can
anyone
explain to me why?

Why is this a circular reference?
 

Thanks a lot mate!!!

"T. Valko" wrote:

CF is different from a worksheet cell formula. The formula used in CF does
not actually reside in the cell like a worksheet formula.

Biff

"CK" wrote in message
...
Now I understand. But the interesting thing is, the formula

=INDIRECT(ADDRESS(ROW(),COLUMN()))0

actually works in conditional formatting.

"T. Valko" wrote:

This part of the formula creates the circular reference:

INDIRECT(ADDRESS(ROW(),COLUMN()))0

It refers to the cell that the formula is in thus being a circular
reference.

Biff

"CK" wrote in message
...
Try copy and paste this to anywhere in a blank worksheet:

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))0,INDIRECT( ADDRESS(18,COLUMN()))=TRUE)

On my machine, Excel keeps saying this is a circular reference. Can
anyone
explain to me why?

Why is this a circular reference?
 

You're welcome!

Biff

"CK" wrote in message
...
Thanks a lot mate!!!

"T. Valko" wrote:

CF is different from a worksheet cell formula. The formula used in CF
does
not actually reside in the cell like a worksheet formula.

Biff

"CK" wrote in message
...
Now I understand. But the interesting thing is, the formula

=INDIRECT(ADDRESS(ROW(),COLUMN()))0

actually works in conditional formatting.

"T. Valko" wrote:

This part of the formula creates the circular reference:

INDIRECT(ADDRESS(ROW(),COLUMN()))0

It refers to the cell that the formula is in thus being a circular
reference.

Biff

"CK" wrote in message
...
Try copy and paste this to anywhere in a blank worksheet:

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()))0,INDIRECT( ADDRESS(18,COLUMN()))=TRUE)

On my machine, Excel keeps saying this is a circular reference. Can
anyone
explain to me why?